生成给定月份的一系列周间隔 [英] Generate series of week intervals for given month

问题描述

在Postgres 9.1数据库中，我试图为给定的月份生成一系列星期，但是有一些限制。我需要所有的星期才能从星期一开始，然后在它们开始或在另一个月结束时被削减。

In a Postgres 9.1 database, I am trying to generate a series of weeks for a given month but with some constraints. I need all weeks to start on Monday and get cut when they start or end in another month.

示例：

2013年2月，我想生成一个这样的序列：

For February, 2013 I want to generate a series like this:

         start
------------------------
2013-02-01 00:00:00+00
2013-02-04 00:00:00+00
2013-02-11 00:00:00+00
2013-02-18 00:00:00+00
2013-02-25 00:00:00+00

我现在拥有的查询如下：

The query that I have now looks like this:

SELECT GREATEST(date_trunc('week', dates.d),
                date_trunc('month',dates.d)) as start
FROM generate_series(to_timestamp(1359676800),to_timestamp(1362095999), '1 week') as dates(d)

此查询让我获得了前4周的服务，但是从25日开始缺少一周。

This query gets me the first 4 weeks but it's missing the week from the 25th. Is it possible to get the last week?

推荐答案

select
    greatest(date_trunc('week', dates.d), date_trunc('month',dates.d)) as start
from generate_series('2013-02-01'::date, '2013-02-28', '1 day') as dates(d)
group by 1
order by 1

这篇关于生成给定月份的一系列周间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！