Stranice

Thursday 20 November 2014

Generating ranges in PostgreSQL


There are great features using PostgreSQL RDBMS, and that are common table expressions (CTE), windowing function, and time ranges. There are interesting inbuilt function for testing purposes and this generates_series(). It supports generating integers, big integers, timestamps with and without time zone but it doesn't support ranges. As I have been learning about this great database I realized that it lack support for generating ranges. This is only core part of functions that will be build for generating ranges in PostgreSQL


with gen(ts) as 
(
select generate_series('2014-09-17'::timestamp,
                        '2015-01-07'::timestamp, 
                        '2 week'::interval)
),
ss(start, stop) as (
select ts, lead(ts, 1) over () from gen
)
select tsrange(start,stop) as "Genijalno" 
from ss 
where stop > start;

The result of the expression above is:

                  Genijalno
-----------------------------------------------
 ["2014-09-17 00:00:00","2014-10-01 00:00:00")
 ["2014-10-01 00:00:00","2014-10-15 00:00:00")
 ["2014-10-15 00:00:00","2014-10-29 00:00:00")
 ["2014-10-29 00:00:00","2014-11-12 00:00:00")
 ["2014-11-12 00:00:00","2014-11-26 00:00:00")
 ["2014-11-26 00:00:00","2014-12-10 00:00:00")
 ["2014-12-10 00:00:00","2014-12-24 00:00:00")
 ["2014-12-24 00:00:00","2015-01-07 00:00:00")
(8 rows)