Search code examples
postgresqlsetdate-arithmeticgenerate-series

Using generate_series() to produce 30 day date ranges for each day?

My goal is to create a table that looks something like this using PostgreSQL:

date           date_start        date_end
12/16/2015     11/17/2015        12/16/2015
12/17/2015     11/18/2015        12/17/2015
etc.

So that I can then join to a different table to get the aggregations for each date on a rolling 30 day window. I've been doing some research and I think generate_series() is what I want to use, but I am unsure.

Solution

  • Something like this:

    SELECT '2015-12-16'::date + g - 30 AS date_start
     , '2015-12-16'::date + g      AS date_end
FROM   generate_series (0, 25) g; -- number of rows

    I skipped the redundant date column. (You shouldn't use a basic type name as identifier anyways.)

    There is a variant of generate_series() that works with timestamps, but the simple version generating integer numbers is just as good for dates. Maybe even better because you avoid possible confusion with time zones.

    Always use the ISO 8601 format for date literals, which is unambiguous with any datestyle or locale settings.

    Related: