如何修正SQL计算排除周末的时间差时天数显示为0的问题？

需求是计算两个datetime之间的时间差，需要排除周六和周日。根据PHP Carbon的示例，2023-01-10 15:12:24与2023-01-02 10:34:36的时间差应为6 days 4 hours 37 minutes 48 seconds，对应的PHP代码如下：

<?php
require 'vendor\carbon\autoload.php';
use Carbon\CarbonImmutable;
use Carbon\CarbonInterval;


$created = CarbonImmutable::parse("2023-01-02 10:34:36");
$firstResponse = CarbonImmutable::parse("2023-01-10 15:12:24");
$diffInSeconds = 0;
$step = $created;

while ($step < $firstResponse) {
    if ($step->isWeekend()) {
        $step = $step->next('Monday');

        continue;
    }

    $nextStep = min($firstResponse, $step->addDay()->startOfDay());

    $diffInSeconds += $step->diffInSeconds($nextStep);
    $step = $nextStep;
}

echo CarbonInterval::seconds($diffInSeconds)->cascade()->forHumans(); //6 days 4 hours 37 minutes 48 seconds

我尝试用递归查询实现该逻辑，但输出结果天数为0：

WITH RECURSIVE date_range AS (
  SELECT '2023-01-02 10:34:36'::timestamp AS date
  UNION ALL
  SELECT CASE
           WHEN EXTRACT(ISODOW FROM date) IN (6, 7) THEN date + INTERVAL '1 day'*(8-EXTRACT(ISODOW FROM date))
           ELSE date + INTERVAL '1 DAY'
         END
  FROM date_range
  WHERE date + INTERVAL '1 DAY' < '2023-01-10 15:12:24'::timestamp
)
SELECT
  CONCAT(
    FLOOR(diff / 86400), ' days ',
    FLOOR((diff % 86400) / 3600), ' hours ',
    FLOOR((diff % 3600) / 60), ' minutes ',
    FLOOR(diff % 60), ' seconds'
  ) AS duration
FROM (
  SELECT
    EXTRACT(EPOCH FROM ('2023-01-10 15:12:24'::timestamp - MAX(date))::interval) AS diff
  FROM date_range
) t;

输出结果：

----------------------------------------
| duration                             |
----------------------------------------
| 0 days 4 hours 37 minutes 48 seconds |
----------------------------------------

请问如何修正该SQL查询以得到正确的天数？

你的递归CTE只记录了几个关键时间点（起始时间、跳过周末后的周一），但最后仅计算了MAX(date)到结束时间的差值，完全忽略了中间完整工作日的整天时长（每个完整工作日为86400秒），因此天数显示为0。

我们需要在递归过程中逐个计算每个时间段的有效秒数并累加，最后再转换为可读格式：

WITH RECURSIVE time_diff AS (
  SELECT
    '2023-01-02 10:34:36'::timestamp AS current_time,
    '2023-01-10 15:12:24'::timestamp AS end_time,
    -- 初始秒数：当前天是工作日则计算到当天结束的秒数，否则为0
    CASE
      WHEN EXTRACT(ISODOW FROM '2023-01-02 10:34:36'::timestamp) IN (6,7) THEN 0
      ELSE EXTRACT(EPOCH FROM LEAST('2023-01-10 15:12:24'::timestamp, ('2023-01-02 10:34:36'::timestamp + INTERVAL '1 day')::date) - '2023-01-02 10:34:36'::timestamp)
    END AS total_seconds
  UNION ALL
  SELECT
    -- 计算下一个时间点：周五则跳至下周一，非周末则跳至次日凌晨，周末则跳至下周一
    CASE
      WHEN EXTRACT(ISODOW FROM current_time) = 5 THEN (current_time + INTERVAL '3 days')::date
      WHEN EXTRACT(ISODOW FROM current_time) IN (6,7) THEN (current_time + INTERVAL (8 - EXTRACT(ISODOW FROM current_time)) || ' days')::date
      ELSE (current_time + INTERVAL '1 day')::date
    END AS current_time,
    end_time,
    -- 累加有效秒数：若下一个时间点早于结束时间且为工作日，加完整天秒数；否则计算剩余时间秒数
    total_seconds + CASE
      WHEN (CASE
             WHEN EXTRACT(ISODOW FROM current_time) = 5 THEN (current_time + INTERVAL '3 days')::date
             WHEN EXTRACT(ISODOW FROM current_time) IN (6,7) THEN (current_time + INTERVAL (8 - EXTRACT(ISODOW FROM current_time)) || ' days')::date
             ELSE (current_time + INTERVAL '1 day')::date
           END) >= end_time THEN 0
      WHEN EXTRACT(ISODOW FROM (CASE
             WHEN EXTRACT(ISODOW FROM current_time) = 5 THEN (current_time + INTERVAL '3 days')::date
             WHEN EXTRACT(ISODOW FROM current_time) IN (6,7) THEN (current_time + INTERVAL (8 - EXTRACT(ISODOW FROM current_time)) || ' days')::date
             ELSE (current_time + INTERVAL '1 day')::date
           END)) IN (6,7) THEN 0
      ELSE EXTRACT(EPOCH FROM LEAST(end_time, (CASE
             WHEN EXTRACT(ISODOW FROM current_time) = 5 THEN (current_time + INTERVAL '3 days')::date
             WHEN EXTRACT(ISODOW FROM current_time) IN (6,7) THEN (current_time + INTERVAL (8 - EXTRACT(ISODOW FROM current_time)) || ' days')::date
             ELSE (current_time + INTERVAL '1 day')::date
           END) + INTERVAL '1 day') - (CASE
             WHEN EXTRACT(ISODOW FROM current_time) = 5 THEN (current_time + INTERVAL '3 days')::date
             WHEN EXTRACT(ISODOW FROM current_time) IN (6,7) THEN (current_time + INTERVAL (8 - EXTRACT(ISODOW FROM current_time)) || ' days')::date
             ELSE (current_time + INTERVAL '1 day')::date
           END))
    END AS total_seconds
  FROM time_diff
  WHERE current_time < end_time
)
SELECT
  CONCAT(
    FLOOR(total_seconds / 86400), ' days ',
    FLOOR((total_seconds % 86400) / 3600), ' hours ',
    FLOOR((total_seconds % 3600) / 60), ' minutes ',
    FLOOR(total_seconds % 60), ' seconds'
  ) AS duration
FROM (
  SELECT MAX(total_seconds) AS total_seconds FROM time_diff
) t;

逻辑说明 #

1. 初始阶段：计算起始时间到当天结束（或结束时间，取较早值）的有效秒数，直接跳过周末。
2. 递归迭代阶段：
   • 每次自动跳至下一个工作日的凌晨时间（自动跳过周六、周日）
   • 若下一个工作日早于结束时间，累加该工作日的完整秒数（86400秒）；若为最后一天，则计算剩余时间段的秒数
3. 结果转换：将累加的总秒数转换为天、时、分、秒的可读格式。

执行该查询后，将得到正确结果：6 days 4 hours 37 minutes 48 seconds

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