Calculating business days (and hours, minutes, seconds) between two datetime in PostgreSQL

Question

How can I calculate the interval between two datetime values, but only count business days (Monday to Friday) using PostgreSQL? Currently, my query is not returning the expected result, which should include the number of days in addition to hours, minutes, and seconds.

WITH cte_temp AS (
    SELECT
        id,
        date_create,
        (SELECT 
            ('2023-02-28 23:59:59'::timestamp - date_create::timestamp) 
            - INTERVAL '1 day' * (EXTRACT(EPOCH FROM ('2023-02-28 23:59:59'::timestamp - date_create::timestamp))::int / 86400) 
        ) AS interval_business
    FROM
        req
    WHERE
        (date_create::timestamp <= CONCAT('2023-02-28',' 23:59:59')::timestamp)
)

SELECT
    *
FROM
    cte_temp

Result :

id	date_create	interval_business
1	2023-02-15T12:45:10.000Z	{"hours":11,"minutes":14,"seconds":49}

The expected result should be :

id	date_create	interval_business
1	2023-02-15T12:45:10.000Z	{"days":11,"hours":11,"minutes":14,"seconds":49}

DB fidle : https://www.db-fiddle.com/f/9cRUog6XGRadVsW6LWogrv/0

EDIT :

My database is readonly

Does [this](https://stackoverflow.com/questions/48954983/counting-number-of-weekdays-between-two-dates) or [this](https://stackoverflow.com/questions/72553281/calculate-the-difference-between-two-dates-in-business-days) or [this](https://stackoverflow.com/questions/67909995/how-do-i-calculate-the-number-of-weekday-between-two-dates-in-postgresql) help — Stu, Apr 12 '23 at 10:35
@Stu it only count the number of days, I need to retreive the interval — executable, Apr 12 '23 at 10:36

score 0 · Answer 1 · answered Apr 12 '23 at 11:26

You may use the same approach as in the linked questions, but exclude weekends from the generated rows:

Generate all days between two dates
Exclude Saturday and Sunday
Count the number of days in the series
Calculate the difference between the upper boundary on your parameter and the last interval end

Below is the query with comments.

create table req(id, ts)
as
select 1, timestamp '2023-02-15 12:45:10'

with params(dateto) as (
  values
    (timestamp '2023-02-20 11:59:59'), /*At Monday to check weekends*/
    (timestamp '2023-02-15 13:59:59'), /*The same day to check less than 1 day*/
    (timestamp '2023-02-28 23:59:59') /*Original*/
)
select
  req.id
  , req.ts
  , params.dateto
  , justify_interval(
    /*Count 1-day intervals (except the last one)*/
    (count(date_val) - 1)*(interval '1 day')
    /*And subtract an extra interval for the last date
      (from the upper boundary to the end of last generated 1-day)*/
    - (max(date_val) - params.dateto)::interval
  ) as duration_
from req
  cross join params
  cross join lateral (
    /*Generate as much full 1-day intervals as covers both dates*/
    select t.date_val
    from generate_series(req.ts,params.dateto + interval '1 day',interval '1 day') as t(date_val)
    /*Exclude weekends*/
    where extract(isodow from t.date_val) not in (6,7)
  ) as times
group by 1,2,3
order by 1,2,3

id	ts	dateto	duration_
1	2023-02-15 12:45:10	2023-02-15 13:59:59	01:14:49
1	2023-02-15 12:45:10	2023-02-20 11:59:59	2 days 23:14:49
1	2023-02-15 12:45:10	2023-02-28 23:59:59	9 days 11:14:49

fiddle

`values` are used just to generate some data, because you didn't specify the source of parameters — astentx, Apr 12 '23 at 12:20

Calculating business days (and hours, minutes, seconds) between two datetime in PostgreSQL

1 Answers1