CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1generate-series
Eddie
Eddie

Reputation: 302

Get partial weeks when generating series in postgresql

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?

Upvotes: 1

Views: 575

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656241

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

Upvotes: 3

mvp
mvp

Reputation: 116048

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)

Upvotes: 1

Related Questions