如何计算两日期间有效工时（排除周末且不使用函数/存储过程）

针对你需要排除周六周日工时的需求，我们可以通过内置日期函数直接计算，无需自定义函数或存储过程。以下是修改后的查询逻辑与完整SQL：

核心逻辑拆解 #

1. 工作日判断：用内置星期函数识别周六、周日，仅统计工作日的工时。
2. 完整工作日工时：算出两个日期间的总完整天数，扣除周末天数后乘以24小时。
3. 首尾部分工时：分别计算StartDate当天（工作日）从起始时间到当日结束的时长，以及EndDate当天（工作日）从当日开始到结束时间的时长。
4. 合并有效工时：将完整工作日工时与首尾部分工时相加，得到最终结果。

完整SQL示例 #

假设你的数据库中dayofweek(date)返回1=周日、2=周一…7=周六（如Teradata）；若为其他数据库（如PostgreSQL用extract(dow from date)，0=周日、6=周六），需调整判断条件。

SELECT
    name,
    StartDate,
    EndDate,
    to_char(StartDate, 'dd/mm/yyyy hh24:mi:ss') AS formatted_start,
    to_char(EndDate, 'dd/mm/yyyy hh24:mi:ss') AS formatted_end,
    -- 计算排除周末的总工时
    to_char(
        -- 完整工作日的工时：(总完整工作日数) * 24
        (
            datediff('day', StartDate, EndDate)
            - (
                -- 统计总天数中的周六数量
                datediff('week', StartDate, EndDate)
                + CASE WHEN dayofweek(StartDate) = 7 THEN 1 ELSE 0 END
                - CASE WHEN dayofweek(EndDate) = 7 THEN 1 ELSE 0 END
            )
            - (
                -- 统计总天数中的周日数量
                datediff('week', StartDate, EndDate)
                + CASE WHEN dayofweek(StartDate) = 1 THEN 1 ELSE 0 END
                - CASE WHEN dayofweek(EndDate) = 1 THEN 1 ELSE 0 END
            )
        ) * 24
        -- 加上StartDate当天的有效工时
        + CASE
            WHEN dayofweek(StartDate) IN (1,7) THEN 0 -- 周末无工时
            ELSE 24 - datediff('hh', DATE(StartDate), StartDate)
          END
        -- 加上EndDate当天的有效工时
        + CASE
            WHEN dayofweek(EndDate) IN (1,7) THEN 0 -- 周末无工时
            ELSE datediff('hh', DATE(EndDate), EndDate)
          END
        -- 同一天时减去重复计算的24小时
        - CASE WHEN DATE(StartDate) = DATE(EndDate) THEN 24 ELSE 0 END
        , 'fm9999999.90'
    ) AS working_hours
FROM
    MyTable
WHERE
    StartDate > '01/01/2022'
    AND EndDate < '08/08/2022'
ORDER BY
    StartDate DESC;

关键说明 #

• 星期规则适配：如果你的数据库星期编号不同（比如PostgreSQL中extract(dow from date)返回0=周日、6=周六），请将dayofweek(StartDate) IN (1,7)改为extract(dow from StartDate) IN (0,6)。
• 精度调整：若需要分钟/秒级精度，可将datediff('hh', ...)替换为分钟计算后除以60，比如datediff('mi', ...)/60.0。
• 同一天处理：当StartDate和EndDate在同一天时，减去重复计算的24小时，避免首尾工时相加后重复统计当日时长。

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