I am new to SQL, where I am trying to split time intervals into multiple rows of one hour, together with an indicator of how many minutes the interval overlaps each hour.
My data:
x
validitybegin | validityend | ticketid |
---------------------+---------------------+------------------+
2017-08-31 12:22:04 | 2017-08-31 13:08:56 | ID1 |
2017-08-31 13:09:02 | 2017-08-31 16:46:51 | ID2 |
2017-08-31 13:09:10 | 2017-08-31 14:09:10 | ID3 |
What I would like:
start | end | overlap | ticketid |
---------------------+---------------------+-------------+------------------+
2017-08-31 12:00:00 | 2017-08-31 13:00:00 | 38 | ID1 |
2017-08-31 13:00:00 | 2017-08-31 14:00:00 | 9 | ID1 |
2017-08-31 13:00:00 | 2017-08-31 14:00:00 | 51 | ID2 |
2017-08-31 14:00:00 | 2017-08-31 15:00:00 | 60 | ID2 |
2017-08-31 15:00:00 | 2017-08-31 16:00:00 | 60 | ID2 |
2017-08-31 16:00:00 | 2017-08-31 17:00:00 | 47 | ID2 |
2017-08-31 13:00:00 | 2017-08-31 14:00:00 | 51 | ID3 |
2017-08-31 14:00:00 | 2017-08-31 15:00:00 | 9 | ID3 |
What would be the easiest/fastest way to do this?
Advertisement
Answer
Try something like this:
select t.start, t.end, t.ticketid,
CASE WHEN EXTRACT(HOUR from t.validityend::timestamp - t.validitybegin::timestamp + interval '30 second') = 0
THEN EXTRACT(MINUTE from date_trunc('minute', t.validityend::timestamp - t.validitybegin::timestamp + interval '30 second')) ELSE
CASE WHEN EXTRACT(HOUR from t.end - t.validitybegin::timestamp + interval '30 second') = 0
THEN EXTRACT(MINUTE from date_trunc('minute', t.end - t.validitybegin::timestamp + interval '30 second')) ELSE
CASE WHEN EXTRACT(HOUR from t.validityend::timestamp - t.start + interval '30 second') = 0
THEN EXTRACT(MINUTE from date_trunc('minute', t.validityend::timestamp - t.start + interval '30 second')) ELSE 60 END
END
END as overlap
from
(
select i.*, generate_series as start, generate_series + interval '1 hour' as end
from intervals i
cross join generate_series
( date_trunc('hour', i.validitybegin::timestamp)
, date_trunc('hour',i.validityend::timestamp)
, '1 hour'::interval)
) t
Result for sample data
start end ticketid overlap
-------------------------------------------------------------------
2017-08-31 12:00:00 2017-08-31 13:00:00 ID1 38
2017-08-31 13:00:00 2017-08-31 14:00:00 ID1 9
2017-08-31 13:00:00 2017-08-31 14:00:00 ID2 51
2017-08-31 14:00:00 2017-08-31 15:00:00 ID2 60
2017-08-31 15:00:00 2017-08-31 16:00:00 ID2 60
2017-08-31 16:00:00 2017-08-31 17:00:00 ID2 47
2017-08-31 13:00:00 2017-08-31 14:00:00 ID3 51
2017-08-31 14:00:00 2017-08-31 15:00:00 ID3 9