2020-09-08

Aggregating Timestamp Ranges with Postgresql

Having timestamp ranges and need to combine those from several lines. Here is a rehearsal to create an user defined aggregate function range_agg with postgresql. Range_agg handles tsrange datatyped input parameter and returns an array of those. At the end there is an usage example with unnest(range_agg(aa)) to receive aggregated and generated ranges. Array is needed because aggregation of ranges can produce several ranges when input ranges do not overlap or meet each other. Ranges and ranges_final functions are used and implements the behavior of the range_agg(tsrange) aggregate.

create or replace function ranges(tsrange[],tsrange)
 returns tsrange[] as 
$$
  select array_append($1,$2);
$$ language 'sql' strict;

create or replace function ranges_final(tsrange[])
 returns tsrange[] as 
$$
with times as (
select st, en, max(newst) over(order by st,en) ledge
  from  (
 select st, en, case when st <= max(le) over(order by st,en) then null else st end as newst
   from (
  select st, en, lag(en) over(order by st, en) le
    from (
   select distinct lower(ra) st, upper(ra) en 
     from (
    select unnest($1) ra
    ) s0
   ) s1
  ) s2
 ) s3
), ranges as (
select ledge st, max(en) en
  from times
 group by ledge
 order by ledge
)
select array_agg(tsrange(st,en))
  from ranges;
$$ language 'sql' strict;

create or replace aggregate range_agg(tsrange)
(
    sfunc = ranges,
    stype = tsrange[], 
    finalfunc = ranges_final,
    initcond = '{}'    
);

select unnest(range_agg(ra)) ra from (
-- 06 - 07
select tsrange(current_date+time'06:00', current_date+time'07:00') ra
  union all
-- 08 - 10
select tsrange(current_date+time'08:00', current_date+time'09:00') ra
  union all
select tsrange(current_date+time'09:00', current_date+time'10:00') ra
  union all
-- 11 - 14
select tsrange(current_date+time'11:00', current_date+time'13:00') ra
  union all
select tsrange(current_date+time'12:00', current_date+time'14:00') ra
) aaa
;

["2020-09-08 06:00:00","2020-09-08 07:00:00")
["2020-09-08 08:00:00","2020-09-08 10:00:00")
["2020-09-08 11:00:00","2020-09-08 14:00:00")

Documentation links: rangetypes, functions-array, functions-aggregate, sql-createaggregate and xaggr

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.