I have the following BigQuery script:

WITH test_table AS (
 SELECT
 CAST('2024-05-02 10:00:00' AS DATETIME) AS start_date,
 CAST('2024-05-06 11:00:00' AS DATETIME) AS end_date

)

SELECT
DATETIME_DIFF(end_date,start_date, second ),
(SELECT 
 COUNT(*) 
 FROM UNNEST(GENERATE_DATE_ARRAY(DATE(start_date), DATE(end_date)))  dt
 WHERE EXTRACT(DAYOFWEEK FROM dt) BETWEEN 6 AND 2
)


FROM test_table

I am trying to work out the working hours and minutes as a decimal, between two dates, and excluding weekends. I am new to BigQuery, and normally I’d handle this as a function, but I cannot do that with our GCP setup.

Essentially I need to work out the working hours between two dates, exclude any weekends, and keep in mind that a working date is between 8:00 and 17:00.

You’ll see I am trying the generate_date_array to do this, but how can I account for start and end time as part of the calc? Essentially I cannot have more than 8 hours per working day?

There is a further requirement, and that is to exclude any time between 12:00 and 13:00 (lunch break), but I first want to see how I can do this before adding that kind of complexity.