Getting different result from table and CTE - Postgresql

executable
March 7, 2023
120 views
1 vote
2 Answers

My goal is to calculate the interval between two date with and without Saturday and Sunday.

If my table look like this :

entity_id	time_c	next_time_c
1	‘2022-06-09 15:39:00’	‘2023-02-27 15:09:48’
2	‘2023-03-06 11:17:12’	‘2023-03-06 11:17:14’

I’m able to calculate the interval using :

WITH parms (entity_id, start_date, end_date) AS
(
    SELECT 
        entity_id,
        time_c::timestamp,
       next_time_c::timestamp
    FROM 
        test_c
), weekend_days (entity_id, wkend) AS 
(
    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
)
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 USING(entity_id)
) sq;

Output (which is correct):

ID	Duration (excluding saturday and sunday)	Duration full
1	186 days 23 hours 30 minutes 48 seconds	{"months":8,"days":22,"hours":23,"minutes":30,"seconds":48}
2	0 days 0 hours 0 minutes 2 seconds	{"seconds":2}

On the other side I have my history table :

entity_id	phase	old_phase	time	next_time
1	‘Log’	null	1654781946989	1654781949732
1	‘Approve’	‘Log’	1654781949732	1654781952676
1	‘Fulfill’	‘Approve’	1654781952676	1677506971778
1	‘Accept’	‘Fulfill’	1677506971778	1677518742552
1	‘Review’	‘Accept’	1677518742552	1678097845979
1	‘Fulfill’	‘Review’	1678097845979	1678097847325
1	‘Accept’	‘Fulfill’	1678097847325	1678097977816
1	‘Review’	‘Accept’	1678097977816	null

The goal is to calculate the interval of the Fulfill phase.

I came up with the following query :

WITH temp2 AS (
    SELECT
        entity_id,
        old_phase,
        phase,
        time,
        next_time,
        to_timestamp(to_char(to_timestamp("time"/1000.0) at time zone 'Europe/Paris', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS') at time zone 'Europe/Paris' AS "TIME 2",
        to_timestamp(to_char(to_timestamp("next_time"/1000.0) at time zone 'Europe/Paris', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS') at time zone 'Europe/Paris'AS "NEXT TIME 2",
        ((next_time - time)/1000.0) AS DIFF
    FROM 
        tbl_history
    WHERE phase = 'Fulfill'
),parms (entity_id, start_date, end_date) AS
(
    SELECT 
        entity_id,
        "TIME 2"::timestamp,
        "NEXT TIME 2"::timestamp
    FROM 
        temp2
), weekend_days (entity_id, wkend) AS 
(
    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
)

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 USING(entity_id)
) sq;

But the output isn’t as expected (should be the same as the previous output) :

ID	Duration (excluding saturday and sunday)	Duration full
1	-76 days 0 hours 0 minutes 2 seconds	{"seconds":2}
1	186 days 22 hours 30 minutes 19 seconds	{"months":8,"days":22,"hours":22,"minutes":30,"seconds":19}

Why do I have a wrong output ?

Demo : https://www.db-fiddle.com/f/ujJe2t9CYLhoRm23RAbSbv/1

Tags: postgresql sql

Answers

I suggest a function to work with your data. It accepts your 2 integer "timestamps" , calculates the number of whole days between them, ignoring weekends. Then the time portions of the start and end are subtracted as an interval which is added back to the number of days as an interval. Hence the overall interval is povided:

CREATE OR REPLACE FUNCTION business_interval(start_time BIGINT, end_time BIGINT)
RETURNS INTERVAL AS $$
DECLARE
    bus_days INTEGER;
    interval_24x7 INTERVAL;
BEGIN
    bus_days := (SELECT COUNT(*) FROM generate_series(
                         to_timestamp(start_time / 1000)::date
                       , to_timestamp(end_time / 1000)::date
                       , '1 day') WHERE EXTRACT(ISODOW FROM generate_series) < 6);

    interval_24x7 := to_timestamp(end_time / 1000)::timestamp 
                   - to_timestamp(start_time / 1000)::timestamp;

    RETURN make_interval(days := bus_days-1) + ((interval_24x7::time)::interval);
  
END;
$$ LANGUAGE plpgsql;

select
      business_interval(time,next_time) as bus_interval
    , *
from tbl_history

bus_interval	entity_id	phase	old_phase	time	next_time
00:00:03	1	Log	null	1654781946989	1654781949732
00:00:03	1	Approve	Log	1654781949732	1654781952676
187 days 23:30:19	1	Fulfill	Approve	1654781952676	1677506971778
03:16:11	1	Accept	Fulfill	1677506971778	1677518742552
5 days 16:51:43	1	Review	Accept	1677518742552	1678097845979
00:00:02	1	Fulfill	Review	1678097845979	1678097847325
00:02:10	1	Accept	Fulfill	1678097847325	1678097977816
null	1	Review	Accept	1678097977816	null

SELECT 8

Here is an alternative approach that doesn’t require a function. Instead it uses lateral joins to mimic the effect of the function provided earlier, a useful feature of this approach is that column aliases formed in the one lateral join can be referenced in any subsequent lateral join which helps simplify the syntax used to create the bus_interval column.

Notes:

In the first lateral join we convert time and next_time into timestamps
in the same lateral using generate_series we can exclude the unwanted weekends, but that result is an integer of whole days which ignores the starting time(of day) and finish time(of day)
in the second lateral join the timestamp values are used to get the whole interval between start_ts and end_ts, interval_24x7 (nb: this is an optional column that can be ommitted)
then we make an interval of the number of business day – 1, plus the hours:minutes:seconds from interval between start_ts and end_ts

CREATE TABLE tbl_history (
    entity_id INT,
    phase VARCHAR(255),
    old_phase VARCHAR(255), 
    time BIGINT, 
    next_time BIGINT
);

INSERT INTO tbl_history VALUES
(1, 'Log', null, 1654781946989, 1654781949732),
(1, 'Approve', 'Log', 1654781949732, 1654781952676),
(1, 'Fulfill', 'Approve', 1654781952676, 1677506971778),
(1, 'Accept', 'Fulfill', 1677506971778, 1677518742552),
(1, 'Review', 'Accept', 1677518742552, 1678097845979),
(1, 'Fulfill', 'Review', 1678097845979, 1678097847325),
(1, 'Accept', 'Fulfill', 1678097847325, 1678097977816),
(1, 'Review', 'Accept', 1678097977816, null);

CREATE TABLE test_c (
    entity_id INT,
    time_c timestamp ,
    next_time_c timestamp 
);

INSERT INTO test_c VALUES
(1, '2022-06-09 15:39:00', '2023-02-27 15:09:48')
,(2, '2023-03-06 11:17:12', '2023-03-06 11:17:14')

SELECT
    t.entity_id
  , t.phase
  , t.old_phase
  , ts.bus_days
  , bd.bus_interval
  , justify_interval(bd.bus_interval) as alt_interval
  , bd.interval_24x7
  , ts.start_ts
  , ts.end_ts
  , t.time
  , t.next_time
FROM tbl_history AS t
inner join lateral (
  /* this lateral join "extends" the source row with these columns */
  SELECT 
       to_timestamp(t.time / 1000)::timestamp      AS start_ts
     , to_timestamp(t.next_time / 1000)::timestamp AS end_ts
     , (SELECT COUNT(*)::int 
        FROM generate_series(to_timestamp(t.time / 1000)::DATE, to_timestamp(t.next_time / 1000)::DATE, '1 day')
        WHERE EXTRACT(ISODOW FROM generate_series) < 6
        ) AS bus_days
  ) AS ts ON true
inner join lateral (
  /* additional lateral join "further extends" the row with these columns 
     and it can access the columns produced by earlier lateral joins
  */
  SELECT 
    ts.end_ts - ts.start_ts as interval_24x7
  , make_interval(0,0,0,ts.bus_days - 1) + ((ts.end_ts - ts.start_ts)::time)::interval  as bus_interval
  ) as bd on true

entity_id	phase	old_phase	bus_days	bus_interval	alt_interval	interval_24x7	start_ts	end_ts	time	next_time
1	Log	null	1	00:00:03	00:00:03	00:00:03	2022-06-09 14:39:06	2022-06-09 14:39:09	1654781946989	1654781949732
1	Approve	Log	1	00:00:03	00:00:03	00:00:03	2022-06-09 14:39:09	2022-06-09 14:39:12	1654781949732	1654781952676
1	Fulfill	Approve	188	187 days 23:30:19	6 mons 7 days 23:30:19	262 days 23:30:19	2022-06-09 14:39:12	2023-02-27 14:09:31	1654781952676	1677506971778
1	Accept	Fulfill	1	03:16:11	03:16:11	03:16:11	2023-02-27 14:09:31	2023-02-27 17:25:42	1677506971778	1677518742552
1	Review	Accept	6	5 days 16:51:43	5 days 16:51:43	6 days 16:51:43	2023-02-27 17:25:42	2023-03-06 10:17:25	1677518742552	1678097845979
1	Fulfill	Review	1	00:00:02	00:00:02	00:00:02	2023-03-06 10:17:25	2023-03-06 10:17:27	1678097845979	1678097847325
1	Accept	Fulfill	1	00:02:10	00:02:10	00:02:10	2023-03-06 10:17:27	2023-03-06 10:19:37	1678097847325	1678097977816
1	Review	Accept	0	null	null	null	2023-03-06 10:19:37	null	1678097977816	null

See this operating here dbfiddle

Please signup or login to give your own answer.

Click here to cancel reply.

Getting different result from table and CTE – Postgresql

Answers