如何计算起止时间小时差并汇总工作记录的总工时与次数？

以下是满足你需求的修改后SQL语句，同时会说明关键调整点：

SELECT
    wst.id,
    wst.start_time AS start,
    wst.end_time AS end,
    l.name,
    -- 计算单条记录的起止小时差（保留2位小数）
    ROUND(EXTRACT(EPOCH FROM (wst.end_time - wst.start_time)) / 3600, 2) AS single_work_hours,
    -- 汇总指定用户该年月的总工时
    SUM(ROUND(EXTRACT(EPOCH FROM (wst.end_time - wst.start_time)) / 3600, 2)) OVER () AS total_work_hours,
    -- 汇总指定用户该年月的工作次数
    COUNT(*) OVER () AS total_work_times
FROM workers_send_times wst
INNER JOIN workers_plan wp ON wp.id = wst.workers_plan_id
LEFT JOIN location_orders lo ON lo.id = wp.location_orders_id
LEFT JOIN location l ON l.id = lo.location_id
WHERE wst.user_id = $2
  -- 修正时间筛选逻辑：确保记录属于指定年月（以start_time为准，若需end_time也在同月可追加对应条件）
  AND EXTRACT(YEAR FROM wst.start_time) = $6
  AND EXTRACT(MONTH FROM wst.start_time) = $3
ORDER BY wst.id, wst.start_time;

关键调整说明 #

1. 单条工时计算：通过EXTRACT(EPOCH FROM 时间差)获取时间差的秒数，除以3600转为小时单位，用ROUND函数控制小数位数提升可读性。
2. 汇总字段实现：使用窗口函数OVER ()，无需分组即可在每条明细记录上展示全局的总工时和总工作次数，同时保留所有明细数据。
3. 时间筛选修正：原查询分别取start的月份和end的年份，逻辑存在漏洞，调整为统一基于start_time的年月筛选；若需确保记录的结束时间也在指定年月，可追加AND EXTRACT(YEAR FROM wst.end_time) = $6 AND EXTRACT(MONTH FROM wst.end_time) = $3。
4. 排序字段修正：原SQL中的send_start_at应为start_time（与表中字段名一致），避免字段不存在的报错。

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