SQL (BigQuery) timediff in minutes between 2 timestamp excluding week-end and only from 5 A.M to 6 P.M

Question

I am quite new to SQL ad I am not sure if this is even possible to create this quid of query on Bigquery. I have 2 columns both in a timestamp format such as 2023-02-03 08:54:05 UTC. The first column is called start and the second end. I need to find the timediff between those 2 colons in minutes but:

1. count only the difference from hours between 5:00 A.M to 6:00 P.M. As an exemple, if start is2023-01-02at5:58 P.Mand end is 2023-01-03 at 5:01 A.M the result should be 3 minutes.

2. exclude saturday and sunday from calculations

Is this possible using BigQuery? I have not seen any documentation to exclude certain time ranges in their time functions documentation on Google Cloud. I know TIME_DIFF() but seems there is no way to exclude a range of hours.

Should I create a table with the list of all saturday and sunday as dates during the next couple of years to help me with this query?

Answer 1

Here you go:

with tbl as (Select datetime "2023-01-02T17:58:00.000" as start, datetime"2023-01-03T05:01:00.000" as ending)

select * ,
timestamp_diff( ending, start, minute ) as total_minutes,
LEAST(datetime_diff( ending, start, minute ) , # case same date
datetime_diff( datetime_add(datetime_trunc(start,day),interval 6+12 hour)  , start, minute ) + # minutes of 1st day
datetime_diff( ending, datetime_add(datetime_trunc(ending,day),interval 5 hour)  , minute )  # minutes of last day
)+
GREATEST(0,
#date_diff(date(ending),date(start),day) # This would count also the weekends, but this excludes the weekends
 case 
    when date_diff(date(ending), date(start), week) > 0 
      then date_diff(date(ending), date(start), day) - (date_diff(date(ending), date(start), week) * 2)
    else
      date_diff(date(ending), date(start), day) 
  end
-1)*12*60 # 12 working hours times 60 minutes
as minutes_between_5am_and_6pm

from tbl

First we calculate the difference from start to end.

Then we calculate the minutes of the first day and the minutes of the last day. We add these numbers together. If this number is greater than from start to end, we use that value instead.

Next we calculate the days between start and end and substract one day, because we already have the first and last day calculated. We ignore negative values and use zero instead. Then we multiply with the working minutes per day.

Since you do not want to have weekends included, I took the query from here. Please have a look on this as well.

Please take into account, that the query does not check that the start and end is between 5 am and 6 pm on a working day. Also public holidays are not excluded from the calculation.