Calculating time intervals considering business days and 24×7

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

Calculating time intervals considering business days and 24x7

问题

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

  1. CREATE TABLE requests (
  2.     entity_id INTEGER,
  3.     type_de_demande TEXT,
  4.     phase TEXT,
  5.     time BIGINT,
  6.     next_time BIGINT
  7. );
  9. INSERT INTO requests (entity_id, type_de_demande, phase, time, next_time)
  10. VALUES
  11.     (3402670, 'SUPPORTREQUEST', 'Log', 1684836635813, 1684836637569),
  12.     (3402670, 'SUPPORTREQUEST', 'Classify', 1684836637569, 1684836638257),
  13.     (3402670, 'SUPPORTREQUEST', 'FirstLineSupport', 1684836638257, 1685090241035),
  14.     (3402670, 'SUPPORTREQUEST', 'Escalate', 1685090241035, 1685090241833),
  15.     (3402670, 'SUPPORTREQUEST', 'Close', 1685090241833, NULL);

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

这是我的SQL查询语句：

  1. SELECT
  2.     t.entity_id,
  3.     t.type_de_demande,
  4.     t.phase,
  5.     bd.bus_interval,
  6.     bd.interval_24x7,
  7.     ts.start_ts,
  8.     ts.end_ts,
  9.     t.time,
  10.     t.next_time
  11. FROM requests AS t
  12. inner join lateral (
  13.     SELECT 
  14.         to_timestamp(t.time / 1000)::timestamp AS start_ts,
  15.         to_timestamp(t.next_time / 1000)::timestamp AS end_ts,
  16.         (SELECT COUNT(*)::int 
  17.          FROM generate_series(to_timestamp(t.time / 1000)::DATE, to_timestamp(t.next_time / 1000)::DATE, '1 day')
  18.          WHERE EXTRACT(ISODOW FROM generate_series) < 6
  19.         ) AS bus_days
  20. ) AS ts ON true
  21. inner join lateral (
  22.     SELECT 
  23.         ts.end_ts - ts.start_ts as interval_24x7,
  24.         make_interval(0,0,0,ts.bus_days - 1) + ((ts.end_ts - ts.start_ts)::time)::interval as bus_interval
  25. ) 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 :

  1. CREATE TABLE requests (
  2.     entity_id INTEGER,
  3.     type_de_demande TEXT,
  4.     phase TEXT,
  5.     time BIGINT,
  6.     next_time BIGINT
  7. );
  9. INSERT INTO requests (entity_id, type_de_demande, phase, time, next_time)
  10. VALUES
  11. 	(3402670, &#39;SUPPORTREQUEST&#39;, &#39;Log&#39;, 1684836635813, 1684836637569),
  12. 	(3402670, &#39;SUPPORTREQUEST&#39;, &#39;Classify&#39;, 1684836637569, 1684836638257),
  13. 	(3402670, &#39;SUPPORTREQUEST&#39;, &#39;FirstLineSupport&#39;, 1684836638257, 1685090241035),
  14. 	(3402670, &#39;SUPPORTREQUEST&#39;, &#39;Escalate&#39;, 1685090241035, 1685090241833),
  15. 	(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 :

  1. SELECT
  2. 	t.entity_id,
  3.     t.type_de_demande,
  4.     t.phase,
  5.     bd.bus_interval,
  6.     bd.interval_24x7,
  7.     ts.start_ts,
  8.     ts.end_ts,
  9.     t.time,
  10.     t.next_time
  11. FROM requests AS t
  12. inner join lateral (
  13. 	SELECT 
  14.     	to_timestamp(t.time / 1000)::timestamp      AS start_ts,
  15.         to_timestamp(t.next_time / 1000)::timestamp AS end_ts,
  16.         (SELECT COUNT(*)::int 
  17.          FROM generate_series(to_timestamp(t.time / 1000)::DATE, to_timestamp(t.next_time / 1000)::DATE, &#39;1 day&#39;)
  18.          WHERE EXTRACT(ISODOW FROM generate_series) &lt; 6
  19.         ) AS bus_days
  20. ) AS ts ON true
  21. inner join lateral (
  22.     SELECT 
  23.         ts.end_ts - ts.start_ts as interval_24x7,
  24.         make_interval(0,0,0,ts.bus_days - 1) + ((ts.end_ts - ts.start_ts)::time)::interval  as bus_interval
  25. ) 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时间戳。

  1. (SELECT COUNT(*)::int 
  2.          FROM generate_series(to_timestamp(t.time / 1000), to_timestamp(t.next_time / 1000), '1 day')
  3.          WHERE generate_series <= to_timestamp(t.next_time / 1000) AND EXTRACT(ISODOW FROM generate_series) < 6
  4.         ) 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.

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

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

发表评论

匿名网友

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

确定