PostgreSQL：将bigint类型毫秒时间转带夏令时的日期时间

你遇到的问题核心是to_timestamp()默认返回的是UTC时间，没有自动适配带夏令时规则的本地时区。要得到符合预期的结果，需要两步操作：将UTC时间转换为目标时区的本地时间，再格式化成你想要的日期样式。

具体解决方案 #

使用AT TIME ZONE子句处理时区转换（它会自动识别并应用夏令时规则），搭配to_char()完成格式输出：

SELECT
  username,
  created_timestamp,
  to_char(
    to_timestamp(created_timestamp / 1000) AT TIME ZONE 'Europe/London',
    'DD.MM.YYYY, HH24:MI'
  ) AS local_time
FROM user_entity;

代码细节解释 #

1. to_timestamp(created_timestamp / 1000)：把毫秒级的bigint数值转换成UTC时区的时间戳（timestamptz类型），这是转换的基础。
2. AT TIME ZONE 'Europe/London'：将UTC时间转换为指定时区的本地时间。这里选择伦敦时区是因为它完全匹配你的示例：夏季启用夏令时（UTC+1），冬季恢复标准时间（UTC+0），PostgreSQL会根据日期自动判断是否应用夏令时偏移。
3. to_char(..., 'DD.MM.YYYY, HH24:MI')：把转换后的本地时间格式化成你需要的日.月.年, 小时:分钟样式。

注意事项 #

• 替换'Europe/London'为你实际需要的时区，比如'Europe/Berlin'、'Asia/Shanghai'等。你可以执行SELECT * FROM pg_timezone_names;查看PostgreSQL支持的所有时区列表。
• 确保你的PostgreSQL版本较新，旧版本可能对部分时区的夏令时规则更新不及时。

匹配你的测试示例 #

针对你给出的测试数据：

• 1567066962775（对应UTC时间2019-08-29 08:22:42）转换为伦敦夏令时后，输出29.08.2019, 09:22
• 1581145146219（对应UTC时间2020-02-08 06:59:06）转换为伦敦标准时间后，输出08.02.2020, 06:59
  完全符合你的预期结果。

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