1

I have two columns with a timestamp in each

column_a               column_b
2021-08-03 13:22:29    2021-08-09 15:51:59

I want to calculate the difference in hours, but exclude the weekend (if the dates fall on or between the two timestamps).

I have tried TIMESTAMPDIFF and HOURS_BETWEEN - but these would still include the weekend.

UPDATE: my solution was to ... create a function to calculate the number of days between the two days, excluding weekends taken from here How to get the WORKING day diff in db2 without saturdays and sundays?

Then in my SELECT used Db2s native DATEDIFF(8,xxx,yyy) to get the total number of hours, and subtracted from this DATEDIFF, the value returned from the function * 24 (for hours)

Hogstrom
  • 3,581
  • 2
  • 9
  • 25
simadams3
  • 15
  • 3
  • How long is your weekend? Did you try to subtract? You can use DAYOFWEEK to find out if the given timestamp is on the weekend days. – data_henrik Sep 21 '21 at 06:50
  • Weekend is Saturday and Sunday. The timestamp values in column_a and column_b would be forever changing. Sometimes the timestamp might fall on on value where I could use DAYOFWEEK, sometimes the weekend might be in the middle of the column_a to column_b range, and other various combinations. – simadams3 Sep 21 '21 at 07:13
  • What you are saying is that it boils down to a simple IF / ELSE (=> CASE) logic you could implement. Give it a shot and report back. – data_henrik Sep 21 '21 at 07:52
  • What is the largest difference (could it be 30 days which would potentially include multiple weekend days? What about national holidays which is much more complicated. – Hogstrom Sep 29 '21 at 15:36

1 Answers1

0
WITH 
  MYTAB (A, B) AS 
  (
    VALUES ('2021-08-03 13:22:29'::TIMESTAMP, '2021-08-09 15:51:59'::TIMESTAMP)
  )
, MYTAB2 (A, B) AS 
(
SELECT 
  CASE WHEN DAYOFWEEK(A) IN (1, 7) THEN DATE (A) + 1 DAY ELSE A END 
, CASE WHEN DAYOFWEEK(B) IN (1, 7) THEN B - (MIDNIGHT_SECONDS (B) + 1) SECOND ELSE B END  
FROM MYTAB
)
, R (TS) AS 
(
SELECT V.TS
FROM MYTAB2 T, TABLE (VALUES T.A, T.B) V (TS)
WHERE T.A <= T.B
  UNION ALL
SELECT DATE (R.TS) + 1 DAY 
FROM R, MYTAB2 T
WHERE DATE (R.TS) + 1 DAY < DATE (T.B)
)
SELECT
COALESCE
(
-- Seconds between start and end
  (DAYS (MAX (TS)) - DAYS (MIN (TS))) * 86400 
+ MIDNIGHT_SECONDS (MAX (TS)) - MIDNIGHT_SECONDS (MIN (TS))
-- SUM of seconds for weekend days is subtracted
- SUM (CASE WHEN DAYOFWEEK (TS) IN (1, 7) THEN 86400 ELSE 0 END)
, 0
) / 3600 AS HRS  
FROM R

The idea is to construct the following table with Recursive Common Table expression first:

2021-08-03 13:22:29 --> 2021-08-04 00:00:00 (if start is a weekend)
2021-08-04 00:00:00 --> 2021-08-05 00:00:00 (if start is a weekend)
...
2021-08-08 00:00:00 --> 2021-08-07 00:00:00 (if end is a weekend)
2021-08-09 15:51:59 --> 2021-08-08 23:59:59 (if end is a weekend)

That is: one timestamp for each day between the start and the end timestamps. These start and end timestamps are adjusted:

  • If start is a weekend - change it to the start of the next day
  • If end is a weekend - change it to the end of the previous day

The final calculation is simple: we subtract sum of seconds for all weekends in the list from the difference in seconds between start and end.

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16