SQL (BigQuery) Grouping Runtime Per Day

This is an interval overlap problem. You can solve this by splitting each time period into separate days and then looking at the overlap for each day:

with t as (
      select 'Computer1' as computername, datetime '2020-06-10T21:01:28' as startdatetime, datetime '2020-06-10T21:20:19' as enddatetime union all
      select 'Computer1' as computername, datetime '2020-06-10T22:54:01' as startdatetime, datetime '2020-06-11T05:21:48' as enddatetime union all
      select 'Computer2' as computername, datetime '2020-06-08T09:11:54' as startdatetime, datetime '2020-06-10T11:36:27' as enddatetime 
     )
select dte, t.computername,
       sum(case when enddatetime >= dte and
                     startdatetime < date_add(dte, interval 1 day)
                then datetime_diff(least(date_add(dte, interval 1 day), enddatetime), 
                                   greatest(dte, startdatetime),
                                   second)
           end) as runtime_seconds
from (select t.*, 
             generate_date_array(date(t.startdatetime), date(t.enddatetime), interval 1 day) gda
      from t 
     ) t cross join
     unnest(gda) dte
group by dte, t.computername;

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top