Get partial weeks when generating series in postgresql

Question

I am trying to generate a series of dates by week from March 1st, 2013 at 00:00:00 to March 14, 2013 at 23:59:59. The query that I have until now looks like this

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

Output:

         start
------------------------
 2013-03-01 00:00:00+00
 2013-03-04 00:00:00+00

The query partially works because it returns the first two weeks as you can see but it's missing the week from March 11, 2013 to March 14, 2013. Is there any way to get the last week even though it has not ended?

score 3 · Accepted Answer · edited May 23 '17 at 12:31

3

Faster:

SELECT generate_series(date_trunc('week', '2013-03-01'::date + 6)
                      ,date_trunc('week', '2013-03-14'::date)
                     ,'1 week')::date AS day
UNION  SELECT '2013-03-01'::date
ORDER  BY 1;

For a detailed explanation see related answer:
Generate series of a month by week interval in Postgresql

edited May 23 '17 at 12:31

Community

1
1

answered Mar 15 '13 at 18:47

Erwin Brandstetter

605,456
145
1,078
1,228

score 1 · Answer 2 · answered Mar 15 '13 at 06:29

You ask generate_series() to start from March 1st at midnight and finish on exactly 2 weeks minus 1 second.

However, in requested interval, only 2 values exactly 1 week apart can be generated. If you extend you interval by just 1 second, then generate_series() will generate 3rd row and your query will start working as you expect - SQLFiddle demo:

SELECT GREATEST(
    date_trunc('week', dates.d), 
    date_trunc('month',dates.d)) as start
FROM generate_series(
    to_timestamp(1362096000),
    to_timestamp(1363305599+1), '1 week') as dates(d)

Get partial weeks when generating series in postgresql

2 Answers2