Snowflake SQL: Calculate Business Hours Between Two Timestamps

A UDF that is analogous to DATEDIFF(‘hours’) but for working hours only

A common business problem is calculating the number of working days or hours between two timestamps. We define working hours as time spent between a start time (say 9am) and end time (say 6pm) on working days, excluding holidays. For example, a project started at 5pm on Monday and completed at 2pm on Tuesday spanned 6 working hours (5pm-6pm on Monday and 9am-2pm on Tuesday).

There is a concise solution to calculating elapsed weekdays described here. However, this approach does not account for holidays and does not easily extend to calculating working hours.

In this article, we will outline an approach to efficiently calculating the working hours between two timestamps, making adjustments for holidays.

First Steps

First, we’ll need to construct a reference table that contains all the time intervals that we consider “working hours.” In this table, we have one row per day indicating whether it is a working day, whether it is a holiday, and the start and end times for the working day.

There are several reasons for this approach:

1. Ease of use — this allows us to construct a concise query that calculates working hours (see below).
2. Auditability — it is straightforward to check which days are being counted as working days, especially if your company has non-standard holidays.
3. Flexibility — this approach allows us to set non-standard working hours for particular days (for example, a half-day). To give a more interesting example, we can also have multiple intervals for the same day. For example, if your working hours include a lunch break from 12pm to 1pm, you can have two rows for the same day with two intervals, 9am to 12pm and 1pm to 6pm.

This Google Sheet contains a basic implementation of our working hours reference table.

1. Working Hours Table

Outline of the solution

Now that we have our reference table, let’s assume we have an input table of “time worked” intervals and that we want to find the working hours elapsed for each work interval. We are going to take the following approach to calculate this result:

1. For each work interval, filter the reference table to just intervals that intersect with it (the reference intervals).
2. Calculate the intersection of the work interval with every reference interval.
3. Calculate the time difference of each intersection.
4. Sum the time differences over all the reference intervals.

Let’s unpack step 2 in a little more detail. There’s a very simple algorithm to calculate the intersection of two intervals. Let’s say our intervals are (a1, a2) and (b1, b2). You can verify using the following graphic that the intersection of the two intervals is given by (max(a1, b1), min(a2, b2)).

With those steps in place, we are ready to write some code!

Implementation in SQL

Here is an example of how we can implement the above algorithm in SQL.

Implementation as a Javascript UDF

For our use case, we chose to implement this as a Javascript UDF in Snowflake, making it easy to reuse the logic to calculate working hours throughout our project.

This code converts the cross join into a for loop, iterating over the days in the range between the start and end times and applying the same interval intersection logic.