Calculating time intervals considering business days and 24×7

huangapple go评论85阅读模式
英文:

Calculating time intervals considering business days and 24x7

问题

我有以下的Postgresql结构和数据:

CREATE TABLE requests (
    entity_id INTEGER,
    type_de_demande TEXT,
    phase TEXT,
    time BIGINT,
    next_time BIGINT
);

INSERT INTO requests (entity_id, type_de_demande, phase, time, next_time)
VALUES
    (3402670, 'SUPPORTREQUEST', 'Log', 1684836635813, 1684836637569),
    (3402670, 'SUPPORTREQUEST', 'Classify', 1684836637569, 1684836638257),
    (3402670, 'SUPPORTREQUEST', 'FirstLineSupport', 1684836638257, 1685090241035),
    (3402670, 'SUPPORTREQUEST', 'Escalate', 1685090241035, 1685090241833),
    (3402670, 'SUPPORTREQUEST', 'Close', 1685090241833, NULL);

目标是计算“time”和“next_time”之间的业务时间间隔(不包括星期六和星期日)和24x7时间。目前,我能够获得正确的24x7时间间隔,但对于业务间隔,我得到了错误的值。

这是我的SQL查询语句:

SELECT
    t.entity_id,
    t.type_de_demande,
    t.phase,
    bd.bus_interval,
    bd.interval_24x7,
    ts.start_ts,
    ts.end_ts,
    t.time,
    t.next_time
FROM requests AS t
inner join lateral (
    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 (
    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 type_de_demande phase bus_interval interval_24x7 start_ts end_ts time next_time
3402670 SUPPORTREQUEST Log {"seconds":2} {"seconds":2} 2023-05-23T10:10:35.000Z 2023-05-23T10:10:37.000Z 1684836635813 1684836637569
3402670 SUPPORTREQUEST Classify {"seconds":1} {"seconds":1} 2023-05-23T10:10:37.000Z 2023-05-23T10:10:38.000Z 1684836637569 1684836638257
3402670 SUPPORTREQUEST FirstLineSupport {"days":3,"hours":22,"minutes":26,"seconds":43} {"days":2,"hours":22,"minutes":26,"seconds":43} 2023-05-23T10:10:38.000Z 2023-05-26T08:37:21.000Z 1684836638257 1685090241035
3402670 SUPPORTREQUEST Escalate {} {} 2023-05-26T08:37:21.000Z 2023-05-26T08:37:21.000Z 1685090241035 1685090241833
3402670 SUPPORTREQUEST Close null null 2023-05-26T08:37:21.000Z null 1685090241833 null

为什么“bus_interval”列有额外的一天{"days":3,"hours":22,"minutes":26,"seconds":43}而不是预期值{"days":2,"hours":22,"minutes":26,"seconds":43}?是否有另一种方法来计算两个Unix时间戳之间的间隔,考虑到业务日和24x7时间?

英文:

I have the following Postgresql structure and data :

CREATE TABLE requests (
    entity_id INTEGER,
    type_de_demande TEXT,
    phase TEXT,
    time BIGINT,
    next_time BIGINT
);

INSERT INTO requests (entity_id, type_de_demande, phase, time, next_time)
VALUES
	(3402670, &#39;SUPPORTREQUEST&#39;, &#39;Log&#39;, 1684836635813, 1684836637569),
	(3402670, &#39;SUPPORTREQUEST&#39;, &#39;Classify&#39;, 1684836637569, 1684836638257),
	(3402670, &#39;SUPPORTREQUEST&#39;, &#39;FirstLineSupport&#39;, 1684836638257, 1685090241035),
	(3402670, &#39;SUPPORTREQUEST&#39;, &#39;Escalate&#39;, 1685090241035, 1685090241833),
	(3402670, &#39;SUPPORTREQUEST&#39;, &#39;Close&#39;, 1685090241833, NULL)

The objective is to calculate the time interval between "time" and "next_time" in business time (excluding Saturday and Sunday) and 24x7 time. Currently, I am able to obtain the correct interval for 24x7 time, but for the business interval, I am getting incorrect values.

Here is my SQL select query :

SELECT
	t.entity_id,
    t.type_de_demande,
    t.phase,
    bd.bus_interval,
    bd.interval_24x7,
    ts.start_ts,
    ts.end_ts,
    t.time,
    t.next_time
FROM requests AS t
inner join lateral (
	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, &#39;1 day&#39;)
         WHERE EXTRACT(ISODOW FROM generate_series) &lt; 6
        ) AS bus_days
) AS ts ON true
inner join lateral (
    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

Result :

entity_id type_de_demande phase bus_interval interval_24x7 start_ts end_ts time next_time
3402670 SUPPORTREQUEST Log {"seconds":2} {"seconds":2} 2023-05-23T10:10:35.000Z 2023-05-23T10:10:37.000Z 1684836635813 1684836637569
3402670 SUPPORTREQUEST Classify {"seconds":1} {"seconds":1} 2023-05-23T10:10:37.000Z 2023-05-23T10:10:38.000Z 1684836637569 1684836638257
3402670 SUPPORTREQUEST FirstLineSupport {"days":3,"hours":22,"minutes":26,"seconds":43} {"days":2,"hours":22,"minutes":26,"seconds":43} 2023-05-23T10:10:38.000Z 2023-05-26T08:37:21.000Z 1684836638257 1685090241035
3402670 SUPPORTREQUEST Escalate {} {} 2023-05-26T08:37:21.000Z 2023-05-26T08:37:21.000Z 1685090241035 1685090241833
3402670 SUPPORTREQUEST Close null null 2023-05-26T08:37:21.000Z null 1685090241833 null

Why does the bus_interval column have an additional day {&quot;days&quot;:3,&quot;hours&quot;:22,&quot;minutes&quot;:26,&quot;seconds&quot;:43} instead of the expected value of {&quot;days&quot;:2,&quot;hours&quot;:22,&quot;minutes&quot;:26,&quot;seconds&quot;:43}? Is there another method to calculate the interval between two Unix timestamps considering both business days and 24x7 time?

Fiddle: https://www.db-fiddle.com/f/axnWR8TUupXywi1ddHDHtX/0

答案1

得分: 1

我认为你的bus_days子查询应该如下所示。我已经删除了::DATE转换,并且只在generate_series时间戳小于等于t.next_time时包括generate_series时间戳。

(SELECT COUNT(*)::int 
         FROM generate_series(to_timestamp(t.time / 1000), to_timestamp(t.next_time / 1000), '1 day')
         WHERE generate_series <= to_timestamp(t.next_time / 1000) AND EXTRACT(ISODOW FROM generate_series) < 6
        ) AS bus_days
英文:

I think your bus_days sub query should be like following. I have removed ::DATE cast and also including generate_series timestamp only when its less than or equal to t.next_time.

(SELECT COUNT(*)::int 
         FROM generate_series(to_timestamp(t.time / 1000), to_timestamp(t.next_time / 1000), &#39;1 day&#39;)
         WHERE generate_series &lt;= to_timestamp(t.next_time / 1000) AND EXTRACT(ISODOW FROM generate_series) &lt; 6
        ) AS bus_days

huangapple
  • 本文由 发表于 2023年6月12日 21:07:32
  • 转载请务必保留本文链接:https://go.coder-hub.com/76456985.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定