When looking a date ranges, is there anyway to have generate_series return the starting and ending dates as well?
select
'2014-06-05 00:00:00'::timestamp + ('1 month'::INTERVAL * s.a) AS date
from
generate_series(1, cast(extract(month from age('2014-09-05'::date, '2014-06-05'::date)) AS integer), 1) as s(a);
Gives this output
date
2014-07-05 00:00:00
2014-08-05 00:00:00
2014-09-05 00:00:00
This is fine, however I would like to have
start_date end_date date
2014-06-05 2014-09-05 2014-07-05 00:00:00
2014-06-05 2014-09-05 2014-08-05 00:00:00
2014-06-05 2014-09-05 2014-09-05 00:00:00
The reason being is that I am extracting multiple start/end pairs from another table but cannot figure out a way of joining them together. I am also using PostgeSQL version 8.2.15 (hence the more complicated generate_series function).
To expand this to my primary problem, I have a table that contains these start and end time pairs.
start_date end_date
2014-08-25 00:00:00 2014-09-25 00:00:00
2014-05-16 00:00:00 2014-08-16 00:00:00
2014-09-09 00:00:00 2014-12-09 00:00:00
2014-06-05 00:00:00 2014-07-05 00:00:00
2014-05-19 00:00:00 2014-08-19 00:00:00
2014-05-15 00:00:00 2014-07-15 00:00:00
2014-09-04 00:00:00 2014-11-04 00:00:00
How can I iterate through this table and join it with the expanded date ranges?
Consider upgrading to a current version. Postgres 8.2 is long dead and forgotten.
For Postgres 8.2 (or later, but there are more elegant solutions in modern Postgres).
Assuming it's all about dates, not timestamps.
Providing start_date and end_date once:
SELECT start_date, end_date
, (start_date + interval '1 month'
* generate_series(1, months))::date AS the_date
FROM (
SELECT extract(month from age(end_date, start_date))::int AS months
, start_date, end_date
FROM (SELECT '2014-06-05'::date AS start_date
, '2014-09-05'::date AS end_date
) data
) sub;
Using column name the_date instead of date which shouldn't be used as identifier.
Drawing values from table t instead:
SELECT start_date, end_date
,(start_date + interval '1 month'
* generate_series(1, months))::date AS the_date
FROM (SELECT *, extract(month FROM age(end_date, start_date))::int AS months
FROM t) sub;
Without subquery
SELECT t_id, start_date, end_date
,(start_date + interval '1 month'
* generate_series(1, extract(month from age(end_date
, start_date))::int)
)::date AS the_date
FROM t;