SQL: Split time interval into 1 hour with overlapping minutes split (Bigquery)

user_11748647 0
Viewed 134

This is the data that I have:

date event_type interval_start interval_end duration_in_min
2022-06-06 s1 09:05:00 11:45:00 160
2022-06-01 s2 08:00:00 08:17:00 17
2022-05-31 c1 17:55:00 18:08:00 13
2022-04-05 s3 07:58:00 08:46:00 48
...

and this is what I would like to achieve:

interval represents a 1 hour interval (or maybe 59 min and 59 sec to be accurate, in case an event starts/ends at exactly 10:00:00 but it should not occur very often).

date interval event_type interval_start interval_end duration_in_min
2022-06-06 09:00:00 s1 09:05:00 11:45:00 55
2022-06-06 10:00:00 s1 09:05:00 11:45:00 60
2022-06-06 11:00:00 s1 09:05:00 11:45:00 45
2022-06-01 08:00:00 s2 08:00:00 08:17:00 17
2022-05-31 17:00:00 c1 17:55:00 18:08:00 5
2022-05-31 18:00:00 c1 17:55:00 18:08:00 8
2022-04-05 07:00:00 s3 07:58:00 08:46:00 2
2022-04-05 08:00:00 s3 07:58:00 08:46:00 46
...

I struggle to sort the data per hour by getting a split for the overlapping minutes into a new interval(s).

Any help would be greatly appreciated :)

google-bigquery intervals
1 Answers

Consider below approach

select 
  date, time(hour, 0, 0) as `interval`, 
  event_type, interval_start, interval_end,
  time_diff(least(time(hour + 1, 0, 0), interval_end), greatest(time(hour, 0, 0), interval_start), minute) as duration_in_min
from your_table, 
unnest(generate_array(0, 23)) hour
where hour between extract(hour from time(interval_start)) and extract(hour from time(interval_end))

if applied to sample data in your question - output is

enter image description here

user_52219440
Related