Using TIME_DIFF with multiple conditions in Google BigQuery

I am trying to calculate the worked hours for specific days in Google BigQuery (SQL).

The pay wage is $10 when you work on a day time but $15 when you work on a night time. Day time is defined as 6am to 10pm whereas night time is defined as 10pm to 6am.

Employees can work flexibly as they are limousine drivers.

The following is an example of my table:

id start_at end_at date
abc123 04:00:00 07:00:00 2020-01-05
abc123 09:00:00 15:32:00 2020-01-05
abc123 23:00:00 23:35:00 2020-01-05
abc123 23:40:00 23:59:00 2020-01-05
abc123 23:59:00 01:35:00 2020-01-05
abc123 02:02:00 04:35:00 2020-01-06
abc123 05:40:00 06:59:00 2020-01-06

So the actual work hours is calculated by taking the difference between start_at and end_at but the day time and night time conditions are becoming a hassle in my query..

*the date column is based on start_at. Even when you start at 11:59pm and end at the next day 12:05am, the date follows the date of the start_at instead of end_at.

Any ideas? Thanks in advance!

2 answers

  • answered 2021-04-08 07:26 Vibhor Gupta

    You can try following code :-

    with mytable as (
      select 'abc123' id, cast( '04:00:00' as time) start_dt, cast( '07:00:00' as time) end_dt, date('2020-01-05' ) date union all
      select 'abc123', cast(    '09:00:00' as time), cast(  '15:32:00' as time), date('2020-01-05') union all
      select 'abc123', cast(    '23:00:00' as time), cast(  '23:35:00' as time), date('2020-01-05' )  union all
    select 'abc123', cast('23:40:00' as time), cast(    '23:59:00' as time), date('2020-01-05') union all
    select 'abc123', cast ('23:59:00' as time), cast(   '01:35:00' as time), date('2020-01-05') union all
    select 'abc123', cast('02:02:00' as time), cast(    '04:35:00' as time), date('2020-01-06') union all
    select 'abc123', cast('05:40:00'  as time), cast(   '06:59:00' as time), date('2020-01-06')
    )
    select id, date, sum (value) as sal from(
    select id, date, 
    case when start_dt > cast(  '06:00:00' as time) and  end_dt < cast( '22:00:00' as time) and start_dt < end_dt  then (time_diff(end_dt, start_dt, Minute)/60) * 10
    when start_dt < cast(   '06:00:00' as time) and  end_dt < cast( '06:00:00' as time) then (time_diff(end_dt, start_dt, Minute)/60) * 15
    when start_dt < cast(   '06:00:00' as time) and  end_dt < cast( '22:00:00' as time) then (time_diff(cast(   '06:00:00' as time), start_dt, Minute)/60) * 15 + (time_diff( end_dt,cast(  '06:00:00' as time), Minute)/60) * 10
    when start_dt > cast(   '22:00:00' as time) and  end_dt < cast( '06:00:00' as time) then (time_diff(cast(   '23:59:00' as time), start_dt, Minute)/60) * 15 + (time_diff( end_dt,cast(  '00:00:00' as time), Minute)/60) * 15
    when start_dt > cast(   '22:00:00' as time) and  end_dt > cast( '22:00:00' as time) then (time_diff(end_dt, start_dt, Minute)/60) * 15
    else 0
    end as value
    from mytable) group by id, date
    

    Output :-

    enter image description here

    You can further group by on month for monthly salary.

  • answered 2021-04-08 21:38 Mikhail Berlyant

    Consider below solution

    create temp function night_day_split(start_at time, end_at time, date date) as (array(
      select as struct 
        extract(date from time_point) day,
        if(extract(hour from time_point) between 6 and 22, 'day', 'night') day_night,
        count(1) minutes
      from unnest(generate_timestamp_array(
          timestamp(datetime(date, start_at)), 
          timestamp(datetime(if(start_at < end_at, date, date + 1), end_at)), 
          interval 1 minute
        )) time_point
      group by 1, 2
    ));
    select id, day, 
      sum(if(day_night = 'day', minutes, null)) day_minutes,
      sum(if(day_night = 'night', minutes, null)) night_minutes
    from yourtable, 
    unnest(night_day_split(start_at, end_at, date)) v
    group by id, day     
    

    if applied to sample data in your question - output is

    enter image description here