生成指定月份的系列周的时间间隔的(Generate series of week intervals

在一个Postgres 9.1数据库，我想生成一系列周对于给定的一个月，但有一些限制。 我需要所有星期开始在星期一和得到，当他们开始或再过一个月切成末。

例：

对于2013年2月，我要产生一系列这样的：

         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

我现在使用的查询如下所示：

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位。 是否有可能在上周获得？

Answer 1:

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

Answer 2:

SELECT generate_series(date_trunc('week', date '2013-02-01' + interval '6 days')
                     , date_trunc('week', date '2013-02-01' + interval '1 month - 1 day')
                     , interval '1 week')::date AS day
UNION  SELECT date '2013-02-01'
ORDER  BY 1;

这种变异并不需要一个子查询， GREATEST或GROUP BY和只生成需要的行。 更简单，更快速。 这是更便宜UNION一行。

• 加6天至一个月的第一天之前date_trunc('week', ...)来计算每月的第一个星期一 。

• 加入1个月和减去前1天date_trunc('week', ...)以获得该月的最后一个星期一 。
  这可以方便地装进一个单一interval表达式： '1 month - 1 day'

• UNION （ 不 UNION ALL ），每月的第一天添加它，除非它已被包含在周一。

• 需要注意的是date + interval导致timestamp ，这是最佳的位置。 详细说明：

  • 在PostgreSQL的两个日期之间生成的时间序列

自动化

你可以提供一个CTE日期系列的开始：

WITH t(d) AS (SELECT date '2013-02-01')  -- enter 1st of month once
SELECT generate_series(date_trunc('week', d + interval '6 days')
                     , date_trunc('week', d + interval '1 month - 1 day')
                     , interval '1 week')::date AS day
FROM   t
UNION  SELECT d FROM t
ORDER  BY 1;

或者把它包装成方便与重复调用一个简单的SQL函数：

CREATE OR REPLACE FUNCTION f_week_starts_this_month(date)
  RETURNS SETOF date AS
$func$
SELECT generate_series(date_trunc('week', $1 + interval '6 days')
                     , date_trunc('week', $1 + interval '1 month - 1 day')
                     , interval '1 week')::date AS day
UNION
SELECT $1
ORDER  BY 1
$func$  LANGUAGE sql IMMUTABLE;

呼叫：

SELECT * FROM f_week_starts_this_month('2013-02-01');

您将通过该月的第一天的日期，但它可以用于任何日期。 你的第一天，所有星期一下一个月。

文章来源: Generate series of week intervals for given month