PostgreSQL中给时间戳加间隔小时并排除周末时段的方法

要实现这个需求，我们需要从ordered_timestamp开始，累计weekday_hours个非周末时段的小时数（即跳过周六00:00至周一00:00的所有时间）。最直观的方式是创建一个PL/pgSQL自定义函数来处理时间的逐步推进，下面是具体步骤：

1. 创建自定义函数 #

这个函数会循环处理剩余的小时数，遇到周末直接跳转到下周一0点，工作日则按剩余小时数逐步累加：

CREATE OR REPLACE FUNCTION add_weekday_hours(p_start timestamp, p_hours int)
RETURNS timestamp
LANGUAGE plpgsql
AS $$
DECLARE
  v_current_time timestamp := p_start;
  v_remaining_hours int := p_hours;
  v_hours_today int;
BEGIN
  WHILE v_remaining_hours > 0 LOOP
    -- 用isodow判断周几：1=周一，6=周六，7=周日
    CASE EXTRACT(isodow FROM v_current_time)
      WHEN 6, 7 THEN
        -- 周六或周日，直接跳转到下周一0点
        v_current_time := date_trunc('week', v_current_time) + INTERVAL '1 week';
      ELSE
        -- 计算当天剩余的可使用小时数（到当天24点）
        v_hours_today := LEAST(
          v_remaining_hours,
          24 - EXTRACT(HOUR FROM v_current_time) - 
          EXTRACT(MINUTE FROM v_current_time)/60 - 
          EXTRACT(SECOND FROM v_current_time)/3600
        )::int;
        
        -- 累加当天的有效小时数
        v_current_time := v_current_time + INTERVAL '1 hour' * v_hours_today;
        v_remaining_hours := v_remaining_hours - v_hours_today;
        
        -- 如果还有剩余小时，跳转到下一天0点
        IF v_remaining_hours > 0 THEN
          v_current_time := date_trunc('day', v_current_time) + INTERVAL '1 day';
        END IF;
    END CASE;
  END LOOP;
  RETURN v_current_time;
END;
$$;

2. 新增并更新due_timestamp列 #

先给表添加新列，再用上面的函数批量计算值：

-- 替换your_table_name为你的实际表名
ALTER TABLE your_table_name ADD COLUMN due_timestamp timestamp;

-- 批量计算到期时间
UPDATE your_table_name
SET due_timestamp = add_weekday_hours(ordered_timestamp, weekday_hours);

3. 验证示例数据 #

我们来验证你提供的两个例子：

• 第一个例子：ordered_timestamp='2020-06-04 16:00:00'（周四），weekday_hours=12
  • 周四剩余8小时（16:00到24:00），累加后剩余4小时，跳转到周五0点
  • 周五累加4小时到04:00，得到2020-06-05 04:00:00，和示例一致
• 第二个例子：ordered_timestamp='2020-06-05 16:00:00'（周五），weekday_hours=48
  • 周五剩余8小时，累加后剩余40小时，跳转到周六0点后直接跳转到周一0点
  • 周一累加24小时，剩余16小时，跳转到周二0点
  • 周二累加16小时到16:00，得到2020-06-09 16:00:00，和示例一致

注意事项 #

• 函数支持任意weekday_hours值（1到数百小时），会自动处理跨多个周末的场景
• 如果需要频繁计算这个值，可以考虑创建生成列，避免手动更新：

  ALTER TABLE your_table_name
  ADD COLUMN due_timestamp timestamp GENERATED ALWAYS AS (add_weekday_hours(ordered_timestamp, weekday_hours)) STORED;
  

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