PostgreSQL如何更新timestamp小时部分且不改变原有日期

问题原因 #

直接对timestamptz（带时区的timestamp）类型字段做interval整体偏移时，计算结果如果跨自然日，会自动连带修改日期部分。原有更新语句的逻辑是把时间点整体往前平移5.5小时，当原时间为凌晨04:30时，往前平移5.5小时自然落到前一天的23:00，不符合仅调整小时、不修改原日期的需求。
另外原语句存在笔误：out_time赋值右侧写为gout_time，执行前需要修正为out_time。

正确实现逻辑 #

核心是把日期和时间拆分处理：

• 先提取原字段的日期作为固定基准，从根源上避免日期被修改
• 单独提取当日的时分秒部分做偏移计算
• 对计算后的时间做模24小时处理，保证偏移后超出0-24点范围的时间自动折算到当日，不会跨天
• 最后把基准日期和折算后的时间拼接为最终的带时区时间值

可直接执行的更新语句 #

UPDATE activity
SET 
  in_time = (in_time::date)::timestamptz 
            + MOD(
                EXTRACT(EPOCH FROM in_time::time) - 5.5 * 3600,
                86400
              ) * INTERVAL '1 second',
  out_time = (out_time::date)::timestamptz 
            + MOD(
                EXTRACT(EPOCH FROM out_time::time) - 5.5 * 3600,
                86400
              ) * INTERVAL '1 second'
WHERE emp_id = 72;

测试用例验证 #

• 输入in_time = 2022-02-03 19:30:00+05:30：提取日期为2022-02-03，当日时间19:30对应70200秒，减5.5小时（19800秒）得50400秒即14:00，最终输出2022-02-03 14:00:00+05:30，符合预期
• 输入out_time = 2022-02-03 04:30:00+05:30：提取日期为2022-02-03，当日时间04:30对应16200秒，减19800秒得-3600秒，对86400取模后为82800秒即23:00，最终输出2022-02-03 23:00:00+05:30，符合预期

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