PostgreSQL查询：跨日期计算员工每日OFF工时（不含午休）

核心思路 #

要处理跨日期的OFF请求，关键是将跨日期的请求拆分为单天记录，再针对每一天计算有效OFF时长（扣除午休时间）。

完整查询语句 #

假设你的请求表名为request_info，执行以下SQL即可得到目标结果：

WITH date_range AS (
    -- 生成每个OFF请求覆盖的所有日期
    SELECT
        sender_username,
        start_time,
        end_time,
        generate_series(
            DATE(start_time),
            DATE(end_time),
            INTERVAL '1 day'
        )::DATE AS off_date
    FROM request_info
    WHERE type = 'OFF'
),
daily_time_bounds AS (
    -- 定义当天工作时段、午休时段，以及实际OFF的起止时间
    SELECT
        sender_username,
        off_date,
        (off_date || ' 08:30:00')::TIMESTAMP AS work_start,
        (off_date || ' 18:00:00')::TIMESTAMP AS work_end,
        (off_date || ' 12:00:00')::TIMESTAMP AS lunch_start,
        (off_date || ' 13:30:00')::TIMESTAMP AS lunch_end,
        GREATEST(start_time, (off_date || ' 08:30:00')::TIMESTAMP) AS actual_start,
        LEAST(end_time, (off_date || ' 18:00:00')::TIMESTAMP) AS actual_end
    FROM date_range
),
daily_off_calculation AS (
    -- 计算每日有效OFF时长（总时长减去午休重叠时长）
    SELECT
        sender_username,
        off_date AS "Date",
        GREATEST(0, EXTRACT(EPOCH FROM (actual_end - actual_start)) / 3600)
        - GREATEST(0, EXTRACT(EPOCH FROM (
            LEAST(actual_end, lunch_end) - GREATEST(actual_start, lunch_start)
        )) / 3600) AS "Off(hours)"
    FROM daily_time_bounds
)
SELECT
    sender_username AS "Sender username",
    "Date",
    ROUND("Off(hours)", 1) AS "Off(hours)"
FROM daily_off_calculation
WHERE "Off(hours)" > 0
ORDER BY sender_username, "Date";

语句解释 #

1. date_range：使用generate_series生成请求覆盖的所有日期，把跨天请求拆分成单天记录。
2. daily_time_bounds：
   • 定义当天的工作时段（8:30-18:00）和午休时段（12:00-13:30）
   • 计算当天实际的OFF起止时间：取请求时间与工作时段的交集（比如第一天的实际开始是8:30，因为请求开始早于上班时间；最后一天的实际结束是13:00，因为请求结束早于下班时间）
3. daily_off_calculation：
   • 先计算当天OFF的总时长（单位：小时）
   • 减去OFF时段与午休的重叠时长，得到有效OFF时长
4. 最终查询：格式化输出字段，保留1位小数，过滤无有效时长的记录并排序。

验证样本数据 #

针对你提供的样本输入，执行后会得到：

完全匹配期望结果。

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