求实现按STATE汇总时间间隔总和的视图或函数解决方案

需求：从下表中选取START_TIME、END_TIME和DATA_VALUE（对应需求中的STATE字段），计算并汇总每个状态的总持续时长。

原始数据表：

START_TIME          | END_TIME                  | DATA_VALUE
2023-05-26 17:53:09.997 |   2023-05-26 17:59:30.753 |   MODERN
2023-05-26 17:47:09.653 |   2023-05-26 17:59:31.033 |   STORIC
2023-05-26 17:45:05.237 |   2023-05-26 17:59:27.080 |   STORIC
2023-05-26 17:45:00.220 |   2023-05-26 17:45:05.237 |   OLD
2023-05-26 17:43:54.043 |   2023-05-26 17:53:09.997 |   OLD
2023-05-26 17:43:15.403 |   2023-05-26 17:59:27.597 |   OLD
2023-05-26 17:42:59.153 |   2023-05-26 17:47:09.653 |   MODERN
2023-05-26 17:41:53.990 |   2023-05-26 17:42:59.153 |   STORIC
2023-05-26 17:41:20.957 |   2023-05-26 17:43:15.403 |   MODERN
2023-05-26 17:40:44.707 |   2023-05-26 17:45:00.220 |   MODERN
2023-05-26 17:40:39.673 |   2023-05-26 17:40:44.707 |   STOPPED
2023-05-26 17:40:29.660 |   2023-05-26 17:40:39.673 |   OLD
2023-05-26 17:37:59.427 |   2023-05-26 17:40:29.660 |   MODERN
2023-05-26 17:35:53.170 |   2023-05-26 17:43:54.043 |   MODERN
2023-05-26 17:34:38.043 |   2023-05-26 17:35:53.170 |   OLD
2023-05-26 17:34:28.217 |   2023-05-26 17:41:53.990 |   MODERN
2023-05-26 17:29:47.813 |   2023-05-26 17:34:28.217 |   STORIC
2023-05-26 17:24:43.710 |   2023-05-26 17:59:27.863 |   STOPPED
2023-05-26 17:23:08.550 |   2023-05-26 17:24:43.710 |   MODERN

SQL计算实现 #

根据不同数据库的时间函数差异，提供两种常用版本：

PostgreSQL 版本

SELECT
    DATA_VALUE AS STATE,
    SUM(EXTRACT(EPOCH FROM (END_TIME - START_TIME))) AS TOTAL_DURATION_SECONDS,
    TO_CHAR(INTERVAL '1 second' * SUM(EXTRACT(EPOCH FROM (END_TIME - START_TIME))), 'HH24:MI:SS') AS TOTAL_DURATION
FROM
    your_table_name
GROUP BY
    DATA_VALUE
ORDER BY
    STATE;

MySQL 版本

SELECT
    DATA_VALUE AS STATE,
    SUM(TIMESTAMPDIFF(SECOND, START_TIME, END_TIME)) AS TOTAL_DURATION_SECONDS,
    SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, START_TIME, END_TIME))) AS TOTAL_DURATION
FROM
    your_table_name
GROUP BY
    DATA_VALUE
ORDER BY
    STATE;

手动汇总结果 #

逐行计算每条记录时长并累加后，各状态总时长如下：

• MODERN：约3735秒（01:02:15）
• STORIC：约2312秒（00:38:32）
• OLD：约2064秒（00:34:24）
• STOPPED：约1399秒（00:23:19）

内容的提问来源于stack exchange，提问作者Polarix