PostgreSQL中同地点不同时节的timestamptz自动转换问题

核心解决思路是不要手动指定固定时区偏移（比如-04:00/-05:00），而是使用PostgreSQL内置的时区名称America/New_York——这个时区包含了纽约地区夏令时（EDT，UTC-4）和冬令时（EST，UTC-5）的自动切换规则，数据库会根据日期自动计算对应的UTC偏移。

具体操作方式：

• 插入或查询时，直接带时区名称而非固定偏移：

  -- 7月的纽约时间14:00，会自动识别为夏令时（UTC-4）
  SELECT '2024-07-01 14:00:00 America/New_York'::timestamptz;
  -- 11月的纽约时间14:00，会自动识别为冬令时（UTC-5）
  SELECT '2024-11-01 14:00:00 America/New_York'::timestamptz;
  

• 如果业务场景经常使用纽约时区，可以设置会话默认时区简化输入：

  -- 当前会话生效
  SET TIME ZONE 'America/New_York';
  -- 之后直接输入时间即可，自动套用纽约时区规则
  SELECT '2024-07-01 14:00:00'::timestamptz;
  SELECT '2024-11-01 14:00:00'::timestamptz;
  

• 若要让整个数据库默认使用该时区，可执行（需数据库权限）：

  ALTER DATABASE your_database_name SET TIME ZONE 'America/New_York';
  

注意事项：

• 确保PostgreSQL的时区数据库是最新的，夏令时规则偶尔会有调整，系统级的时区数据更新会同步到PostgreSQL中。
• 固定时区偏移（如-04:00）是静态的，不会随日期切换夏令时，只有使用地区时区名称才能触发自动适配。

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