Timestamp difference in Snowflake

Question

I want to find the time difference between two timestamps for each id. When calculating it, only from 9am till 17pm and weekdays are needed to be accounted.

e.g. for the first record, it must be calculated from 9am on 2021-05-19, hence the result would be 45 minutes. For the second record, it would be 330 minutes, calculated as below:

TIMESTAMPDIFF('minute', '2021-05-19 15:30:00'::timestamp, '2021-05-19 17:00:00'::timestamp) + 
TIMESTAMPDIFF('minute', '2021-05-20 09:00:00'::timestamp, '2021-05-20 13:00:00'::timestamp)

Here is example data:

WITH t1 AS (
SELECT 'A' AS id, '2021-05-18 18:30:00'::timestamp AS started_at, '2021-05-19 09:45:00'::timestamp AS ended_at UNION ALL
SELECT 'B' AS id, '2021-05-19 15:30:00'::timestamp AS started_at, '2021-05-20 13:00:00'::timestamp AS ended_at
    )
SELECT *
FROM t1

Answer 1

You can use the following UDF

create or replace function tsrange_intersection(s string, e string)
  RETURNS double
  LANGUAGE JAVASCRIPT
AS
$$
let minutes = 0
start = new Date(S)
end = new Date(E)
let t = start
while(t < end) {
    if ([1, 2, 3, 4, 5].includes(t.getDay())
      && [9, 10, 11, 12, 13, 14, 15, 16].includes(t.getHours())) {
        minutes += 1
    }
    t = new Date(t.getTime() + 60*1000);
}
return minutes
$$;

This works as follows:

WITH t1 AS (
SELECT 'A' AS id, '2021-05-18 18:30:00' AS started_at, '2021-05-19 09:45:00' AS ended_at UNION ALL
SELECT 'B' AS id, '2021-05-19 15:30:00' AS started_at, '2021-05-20 13:00:00' AS ended_at
    )
SELECT tsrange_intersection(started_at, ended_at) minutes
FROM t1;

MINUTES
45
330