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.
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: