PostgreSQL 计算两个时间之间的日期差
需求：有两个时间，现在想计算两个时间之间相差了多少天，并且如果遇到时间中间有周末则减去 2 天，如果时间之间有一个是周末则减去一天。
WITH date_range AS (
SELECT
'2023-12-20 10:00:00'::TIMESTAMP AS start_date,
'2023-12-28 09:00:00'::TIMESTAMP AS end_date
)
SELECT
start_date::DATE AS start_date,
end_date::DATE AS end_date,
end_date::DATE - start_date::DATE + 1 AS total_days,
CASE
WHEN EXTRACT(ISODOW FROM start_date) = 6 THEN end_date::DATE - start_date::DATE + 1 - 2 -- 如果开始日期是周六，减去 2 天
WHEN EXTRACT(ISODOW FROM start_date) = 7 THEN end_date::DATE - start_date::DATE + 1 - 1 -- 如果开始日期是周日，减去 1 天
ELSE
end_date::DATE - start_date::DATE + 1 -
(SELECT COUNT(*)
FROM generate_series(start_date::DATE, end_date::DATE, '1 day') AS dates
WHERE EXTRACT(ISODOW FROM dates) IN (6, 7)) -- 减去周末的天数
END AS adjusted_days
FROM date_range;