Calculate several timedifference give different output

March 7, 2023

I have the following table :

entity_id	time_c	next_time_c
1	‘2023-01-02 10:34:36’	‘2023-01-10 15:12:24’
2	‘2023-03-01 16:10:12’	‘2023-03-20 22:47:59’

My goal is to calculate the interval between time_c and next_time_c without Saturday and Sunday and the full interval.

I come up with the following query :

WITH parms (entity_id, start_date, end_date) AS
(
    SELECT 
        entity_id,
        time_c::timestamp,
        next_time_c::timestamp
    FROM 
        test_c
), weekend_days (wkend) AS 
(
    SELECT 
        SUM(CASE WHEN EXTRACT(isodow FROM d) IN (6, 7) THEN 1 ELSE 0 END) 
    FROM 
        parms 
    CROSS JOIN 
        generate_series(start_date, end_date, interval '1 day') dn(d)
)
SELECT 
    entity_id AS "ID",
    CONCAT(
        extract(day from diff), ' days ', 
        extract( hours from diff)   , ' hours ', 
        extract( minutes from diff) , ' minutes ', 
        extract( seconds from diff)::int , ' seconds '
    ) AS "Duration (excluding saturday and sunday)",
    justify_interval(end_date::timestamp - start_date::timestamp) AS "Duration full"
FROM (
    SELECT 
        start_date,
        end_date,
        entity_id,
        (end_date-start_date) - (wkend * interval '1 day') AS diff
    FROM parms 
    JOIN weekend_days ON true
) sq;

It works well when I have only 1 row in my table. When there is more than 1 row, the result is wrong.

With 1 row :

ID	Duration (excluding saturday and sunday)	Duration full
1	6 days 4 hours 37 minutes 48 seconds	{"days":8,"hours":4,"minutes":37,"seconds":48}

ID	Duration (excluding saturday and sunday)	Duration full
2	13 days 6 hours 37 minutes 47 seconds	{"days":19,"hours":6,"minutes":37,"seconds":47}

With 2 rows :

ID	Duration (excluding saturday and sunday)	Duration full
1	0 days 4 hours 37 minutes 48 seconds	{"days":8,"hours":4,"minutes":37,"seconds":48}
2	11 days 6 hours 37 minutes 47 seconds	{"days":19,"hours":6,"minutes":37,"seconds":47}

Why the result is false when calculating several rows ?

Demo : https://www.db-fiddle.com/f/mDCS6cSwT1hbvit2WLYZbc/0

>Solution :

You should calculate weekend days for each entity_id separately:

...
    SELECT 
        entity_id,
        SUM(case when extract(isodow from d) in (6, 7) then 1 else 0 end) 
    FROM 
        parms
    CROSS JOIN 
        generate_series(start_date, end_date, interval '1 day') dn(d)
    GROUP BY entity_id
...

Check the full query in Db-Fiddle.