CODEWITHSUNDEEP
postgresqldatetimetimestampdate-arithmetic
kimi
kimi

Reputation: 525

Time difference within business hours

I want to find the time difference between NOW() and a timestamp column.
 When calculating the time difference, only business hours from 9am till 5pm and weekdays needed to be accounted.


e.g. for the first record 2021-04-21 07:56:36, if the now is 2021-04-21 10:00:00, the time difference must be calculated from 9 am, in this case, the result would be 1 hour, like this:

(NOW() - created_at) - ('2021-04-21 09:00'::timestamp - created_at) AS timediff

For the second record with 2021-04-19 21:55:46, the result would be 9 hrs made of 8+1 hrs as from 2021-04-20 09:00 to 2021-04-20 17:00 and 2021-04-21 09:00 till 2021-04-21 10:00:00

For the third record with 2021-04-17 14:22:05, it would be 17 hrs, made of 8+8+1 hrs.

Sample data:

+----------------------------------+---------------------+
|                id                |     created_at      |
+----------------------------------+---------------------+
| 9d38ea6e7077400db310cfc47b31d482 | 2021-04-21 07:56:36 |
| 9d38ea6e7077400db310cfc47b31d481 | 2021-04-19 21:55:46 |
| 9d38ea6e7077400db310cfc47b31d480 | 2021-04-17 14:22:05 |
| 9d38ea6e7077400db310cfc47b31d479 | 2021-04-18 22:43:44 |
+----------------------------------+---------------------+

The desired outcome:

+----------------------------------+---------------------+----------+
|                id                |     created_at      | timediff |
+----------------------------------+---------------------+----------+
| 9d38ea6e7077400db310cfc47b31d482 | 2021-04-21 07:56:36 | 01:00:00 |
| 9d38ea6e7077400db310cfc47b31d481 | 2021-04-19 21:55:46 | 09:00:00 |
| 9d38ea6e7077400db310cfc47b31d480 | 2021-04-17 14:22:05 | 17:00:00 |
| 9d38ea6e7077400db310cfc47b31d479 | 2021-04-18 22:43:44 | 17:00:00 |
+----------------------------------+---------------------+----------+

Upvotes: 1

Views: 383

Answers (1)

Julius Tuskenis
Julius Tuskenis

Reputation: 1630

I'd suggest generating all the ranges of business hours between created_at and current_timestamp and summing them. While it is far from optimal solution in terms of performance I think it is the most straight-forward approach. I use timestamp with time zone range tstzrange with intersection operator (*).

SELECT id,
  created_at,
  SUM(upper(business_range) - lower(business_range) ) business_hours
FROM (
  SELECT id, 
    created_at,
    tstzrange(date_in_range + '09:00'::time with time zone, date_in_range + '17:00'::time with time zone) 
      * tstzrange(created_at, current_timestamp) as business_range
  FROM (
    SELECT id,
      created_at,
      created_at::date + generate_series(0, current_timestamp::date - created_at::date) as date_in_range 
    FROM times
  ) dates_in_range
  WHERE date_part('dow', date_in_range) in (1,2,3,4,5)
) business_hour_ranges
GROUP BY
  id,
  created_at

See the setup and example in db<>fiddle

Word of caution

While the implementation was made to be easy to understand and debug it may result in poor performance - especially if used with very old created_at dates. If that may be the case in your system, you may be better off writing function that checks the day of week of the created_at and current_date, finds the number of days between them and determines work hours.

Upvotes: 2

Related Questions