CODEWITHSUNDEEP
sqlpostgresqlintervalsoverlap
Michal Špondr
Michal Špondr

Reputation: 1545

Splitting interval overlapping more days in PostgreSQL

I have a PostgreSQL table containing start timestamp and duration time.

timestamp           | interval
------------------------------
2018-01-01 15:00:00 | 06:00:00
2018-01-02 23:00:00 | 04:00:00
2018-01-04 09:00:00 | 2 days 16 hours

What I would like is to have the interval splitted into every day like this:

timestamp           | interval
------------------------------
2018-01-01 15:00:00 | 06:00:00
2018-01-02 23:00:00 | 01:00:00
2018-01-03 00:00:00 | 03:00:00
2018-01-04 09:00:00 | 15:00:00
2018-01-05 00:00:00 | 24:00:00
2018-01-06 00:00:00 | 24:00:00
2018-01-07 00:00:00 | 01:00:00

I am playing with generate_series(), width_bucket(), range functions, but I still can't find plausible solution. Is there any existing or working solution?

Upvotes: 0

Views: 188

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51649

not sure about all edge cases, but this seems working:

t=# with c as (select *,min(t) over (), max(t+i) over (), tsrange(date_trunc('day',t),t+i) tr from t)
, mid as (
select distinct t,i,g,tr
, case when g < t then t else g end tt
from c
right outer join (select generate_series(date_trunc('day',min),date_trunc('day',max),'1 day')  g from c) e on g <@ tr order by 3,1
)
select
  tt
, i
, case when tt+'1 day' > upper(tr) and t < g then upper(tr)::time::interval when upper(tr) - lower(tr) < '1 day' then i else g+'1 day' - tt end
from mid
order by tt;
         tt          |        i        |   case
---------------------+-----------------+----------
 2018-01-01 15:00:00 | 06:00:00        | 06:00:00
 2018-01-02 23:00:00 | 04:00:00        | 01:00:00
 2018-01-03 00:00:00 | 04:00:00        | 03:00:00
 2018-01-04 09:00:00 | 2 days 16:00:00 | 15:00:00
 2018-01-05 00:00:00 | 2 days 16:00:00 | 1 day
 2018-01-06 00:00:00 | 2 days 16:00:00 | 1 day
 2018-01-07 00:00:00 | 2 days 16:00:00 | 01:00:00
(7 rows)

also please mind that timestamp without time zone can fail you when comparing timestamps...

Upvotes: 1

Related Questions