PostgreSQL: The joy of generate_series

Using generate_series to build up data as required for reporting using a temporal modeled table.

create table temporal_table (
some_value integer,
date_from timestamp default current_date,
date_to timestamp default '3000/12/31 23:59:59'::timestamp
);

insert into temporal_table values (1, '2010/01/01', '2010/01/05 23:59:59');
insert into temporal_table values (2, '2010/01/06', '2010/01/08 23:59:59');
insert into temporal_table values (3, '2010/01/09', '2010/01/31 23:59:59');
insert into temporal_table values (4, '2010/02/01');

select *
from temporal_table
join
(select generate_series calendar_date
from generate_series('2010/01/01 00:00'::timestamp, '2010/01/31 23:59:59', '1 day')
) as period
on  period.calendar_date between temporal_table.date_from and temporal_table.date_to
where date_from between '2010/01/01' and '2010/01/31 23:59:59'

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s