如何在SQL中将时间戳转为24小时制并计算分组时间差平均值？

需求回顾 #

• 将after_order字段的时间戳转换为24小时制格式
• 计算order与after_order的时间差（单位：秒）
• 按group_fin分组，计算上述时间差的平均值

方案一：保留明细数据并显示分组平均 #

如果需要保留每条记录的详细信息，同时展示对应分组的平均时间差，可使用窗口函数实现：

SELECT
    os.no_order_survey AS 'id',
    os.id_finance_company AS 'group_fin',
    os.created_at AS 'order',
    -- 转换after_order为24小时制（MySQL语法）
    DATE_FORMAT(hs.created_at, '%Y-%m-%d %H:%i:%s') AS 'after_order',
    -- 计算单条记录的时间差（秒）
    TIMESTAMPDIFF(SECOND, os.created_at, hs.created_at) AS 'result_second_from_order_to_after',
    -- 按group_fin分组计算平均时间差
    AVG(TIMESTAMPDIFF(SECOND, os.created_at, hs.created_at)) OVER (PARTITION BY os.id_finance_company) AS 'avg_second_by_group_fin'
FROM
    tr_order_survey os
JOIN
    tr_hasil_survey hs ON os.no_order_survey = hs.no_order_survey
ORDER BY
    os.created_at DESC;

方案二：仅显示分组聚合结果 #

如果只需要按group_fin分组后的平均时间差，可使用普通分组聚合：

SELECT
    os.id_finance_company AS 'group_fin',
    -- 计算该分组的平均时间差（秒）
    AVG(TIMESTAMPDIFF(SECOND, os.created_at, hs.created_at)) AS 'avg_second_from_order_to_after'
FROM
    tr_order_survey os
JOIN
    tr_hasil_survey hs ON os.no_order_survey = hs.no_order_survey
GROUP BY
    os.id_finance_company
ORDER BY
    avg_second_from_order_to_after DESC;

不同数据库适配说明 #

24小时制时间转换

• PostgreSQL：替换DATE_FORMAT为TO_CHAR(hs.created_at, 'YYYY-MM-DD HH24:MI:SS')
• SQL Server：替换DATE_FORMAT为CONVERT(VARCHAR, hs.created_at, 120)（120格式默认24小时制）

时间差计算（秒为单位）

• PostgreSQL：替换TIMESTAMPDIFF为EXTRACT(EPOCH FROM (hs.created_at - os.created_at))
• SQL Server：替换TIMESTAMPDIFF为DATEDIFF(SECOND, os.created_at, hs.created_at)

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