如何在PostgreSQL中计算两个日期之间的工作日天数

核心逻辑：你之前的写法是直接计算两个日期的自然日差，要剔除周末的话，只需要在自然日差基础上扣除区间内的周六、周日总天数即可。

PostgreSQL 适配方案（你原写法使用的::类型转换、数组下标语法符合PostgreSQL特征） #

写法1：日期序列过滤（逻辑直观，适合小数据量场景） #

SELECT 
  COUNT(*) AS work_days
FROM 
  order_items,
  generate_series(created_time_order_items::DATE, created_time_receiving[1]::DATE, '1 day'::interval) AS dt
WHERE 
  EXTRACT(DOW FROM dt) NOT IN (0, 6) -- DOW返回值0为周日、6为周六，直接排除
GROUP BY 
  order_items.id; -- 替换为你表的实际主键字段即可

写法2：公式直接计算（无关联开销，适合大数据量场景） #

SELECT
  (created_time_receiving[1]::DATE - created_time_order_items::DATE)
  -- 扣除完整周对应的2天周末
  - (EXTRACT(WEEK FROM created_time_receiving[1]::DATE) - EXTRACT(WEEK FROM created_time_order_items::DATE)) * 2
  -- 扣除首尾不完整周里的周末天数
  - CASE WHEN EXTRACT(DOW FROM created_time_order_items::DATE) = 0 THEN 1 ELSE 0 END
  - CASE WHEN EXTRACT(DOW FROM created_time_receiving[1]::DATE) = 6 THEN 1 ELSE 0 END
  AS work_days
FROM order_items;

其他常见数据库适配方案 #

• MySQL 8.0+

WITH RECURSIVE date_series AS (
  SELECT created_time_order_items AS dt, id FROM order_items
  UNION ALL
  SELECT dt + INTERVAL 1 DAY, id FROM date_series WHERE dt < created_time_receiving[1]
)
SELECT 
  COUNT(*) AS work_days 
FROM date_series 
WHERE WEEKDAY(dt) NOT IN (5,6) -- WEEKDAY返回值5为周六、6为周日
GROUP BY id;

• SQL Server

SELECT
  DATEDIFF(dd, created_time_order_items, created_time_receiving[1])
  - DATEDIFF(wk, created_time_order_items, created_time_receiving[1]) * 2
  - CASE WHEN DATENAME(dw, created_time_order_items) = 'Sunday' THEN 1 ELSE 0 END
  - CASE WHEN DATENAME(dw, created_time_receiving[1]) = 'Saturday' THEN 1 ELSE 0 END
  AS work_days
FROM order_items

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