PostgreSQL：interval未适配夏令时的问题咨询

问题根源 #

interval '1 months'是绝对时间维度的运算，直接对timestamptz（带时区时间戳）的日期部分加1个月，保持时分秒不变，不会考虑目标时区的夏令时规则。以美国芝加哥时区为例，2023年3月到4月进入夏令时，本地时间会提前1小时，导致直接用timestamptz + interval '1 months'得到的结果，和预期的本地时间差1小时，进而导致判断逻辑出错。

解决方案 #

有两种可靠的方法可以让月份间隔运算适配夏令时：

方法1：先转本地时区timestamp运算，再转回timestamptz

先将timestamptz转换为目标时区的本地timestamp（不带时区），完成月份加法后，再转回timestamptz。这种方式会让PostgreSQL自动根据夏令时规则调整时区偏移，保证本地时间的一致性。

改写你的测试SQL如下：

SELECT
'2023-02-01 15:00:00'::timestamp at time zone 'America/Chicago' < 
  (('2023-01-01 15:00:00'::timestamp at time zone 'America/Chicago') at time zone 'America/Chicago' + interval '1 months') at time zone 'America/Chicago',
'2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago' < 
  (('2023-02-01 15:00:00'::timestamp at time zone 'America/Chicago') at time zone 'America/Chicago' + interval '1 months') at time zone 'America/Chicago',
'2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago' < 
  (('2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago') at time zone 'America/Chicago' + interval '1 months') at time zone 'America/Chicago',
'2023-05-01 15:00:00'::timestamp at time zone 'America/Chicago' < 
  (('2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago') at time zone 'America/Chicago' + interval '1 months') at time zone 'America/Chicago',
'2023-06-01 15:00:00'::timestamp at time zone 'America/Chicago' < 
  (('2023-05-01 15:00:00'::timestamp at time zone 'America/Chicago') at time zone 'America/Chicago' + interval '1 months') at time zone 'America/Chicago'
;

核心逻辑：

1. (timestamptz_val at time zone 'America/Chicago')：把带时区时间戳转成芝加哥本地的无时区时间戳
2. 加interval '1 months'：在本地时间轴上完成月份加法
3. at time zone 'America/Chicago'：转回带时区时间戳，PostgreSQL自动适配夏令时偏移

方法2：基于日期截断的运算

先截断到日期维度，加1个月后再补回原有的时分秒部分，同样能触发时区偏移的自动调整：

SELECT
'2023-02-01 15:00:00'::timestamp at time zone 'America/Chicago' < 
  ((date_trunc('day', '2023-01-01 15:00:00'::timestamp at time zone 'America/Chicago') + interval '1 months')::timestamp + ('2023-01-01 15:00:00'::timestamp at time zone 'America/Chicago' - date_trunc('day', '2023-01-01 15:00:00'::timestamp at time zone 'America/Chicago')))::timestamptz,
'2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago' < 
  ((date_trunc('day', '2023-02-01 15:00:00'::timestamp at time zone 'America/Chicago') + interval '1 months')::timestamp + ('2023-02-01 15:00:00'::timestamp at time zone 'America/Chicago' - date_trunc('day', '2023-02-01 15:00:00'::timestamp at time zone 'America/Chicago')))::timestamptz,
'2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago' < 
  ((date_trunc('day', '2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago') + interval '1 months')::timestamp + ('2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago' - date_trunc('day', '2023-03-01 15:00:00'::timestamp at time zone 'America/Chicago')))::timestamptz,
'2023-05-01 15:00:00'::timestamp at time zone 'America/Chicago' < 
  ((date_trunc('day', '2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago') + interval '1 months')::timestamp + ('2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago' - date_trunc('day', '2023-04-01 15:00:00'::timestamp at time zone 'America/Chicago')))::timestamptz,
'2023-06-01 15:00:00'::timestamp at time zone 'America/Chicago' < 
  ((date_trunc('day', '2023-05-01 15:00:00'::timestamp at time zone 'America/Chicago') + interval '1 months')::timestamp + ('2023-05-01 15:00:00'::timestamp at time zone 'America/Chicago' - date_trunc('day', '2023-05-01 15:00:00'::timestamp at time zone 'America/Chicago')))::timestamptz
;

核心逻辑：

1. date_trunc('day', timestamptz_val)：截断到日期，去掉时分秒
2. 加interval '1 months'：完成日期维度的月份加法
3. 补回原有的时分秒差值，再转回timestamptz，自动适配夏令时

验证效果 #

两种方法都会让2023-03-01和2023-04-01的判断返回正确结果，因为运算过程是在本地时间轴上进行的，夏令时的偏移调整由PostgreSQL自动处理，不会出现1小时的偏差。

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