Time duration between 2 DateTime columns EXCLUDE out of office hours - SQL BigQuery

Question

I have a task requiring tracking SLA times. The aim is to exclude out of hours time from the total SLA time to get a true/fair SLA time.

Parameters:

Working hours for tasks are between 08:00:00 and 14:00:00 (6 hour open window). Any time outside of these hours, the time won't begin until the next day at 08:00:00.

SELECT

TaskID, TaskStartedDateTime, TaskCompletedDateTime

FROM Tasks

Example 1: A task starts at 27/05/2023 18:05:23. The task finishes at 28/05/2023 12:05:23. This task on the surface took 18 hours to complete, but in theory it actually took 4 hours 5 minutes and 23 seconds as the working hours started at 08:00:00 the next day.

Example 2: A task starts at 22/03/2023 07:45:01. The task finishes at 22/03/2023 09:05:16. This task on the surface took 1 hour 20 minutes and 15 seconds to complete, but in theory it actually took 1 hour 5 minutes and 16 seconds as the working hours started at 08:00:00 the next day.

Example 3: A task starts at 18/01/2023 07:45:01. The task finishes at 20/01/2023 09:00:07. This task on the surface took just over 2 days but in reality it was less than 2 days as users weren't completing the task out of hours time.

What I need is a SQL query (BigQuery) that will generate the date/time difference between the TaskStartedDateTime and the TaskCompletedDateTime but EXCLUDE any time outside the Office Hours to get the true duration of office hours/time ONLY.

I'm able to do a CASE WHEN to flag which Tasks had a TaskStartedDateTime out of hours, but unsure on how to get a duration time

score 0 · Answer 1 · answered Jul 02 '23 at 18:04

A solution can be to look for each day, how much was worked on it. This can be done in SQL by a left join of all working days. A better solution is the subquery.

With working_tbl as 
(
 SELECT dates,
  timestamp_add(timestamp(dates),interval 8 hour) starting,
  timestamp_add(timestamp(dates),interval 18 hour) ending,
 from unnest(generate_date_array("2023-01-01","2024-01-01")) dates #put here your working days

)

select *,

(Select sum( 
  greatest(timestamp_diff(
   least(end_user,ending),
   greatest(starting,start_user),
   minute),
  0)
  ) 
 from working_tbl) as duration


from (select timestamp "2023-05-27 18:05:23" as start_user, timestamp "2023-05-28 12:05:00" as end_user )

Time duration between 2 DateTime columns EXCLUDE out of office hours - SQL BigQuery

1 Answers1