PostgreSQL导入CSV后，如何让时间戳识别夏令时/冬令时？

首先，先明确几个关键点：你的核心操作其实是正确的，但可能因为只查看了冬令时时间段的记录，才误以为没有识别夏令时。咱们一步步拆解：

为什么你的转换语句是有效的 #

你用的转换逻辑：

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + timestamp::integer * INTERVAL '1 second' AS timestamp FROM table;

本质上是把UTC时间戳（epoch）转换为数据库时区（Europe/Berlin）下的带时区时间戳（timestamptz类型）。PostgreSQL会自动根据Europe/Berlin时区规则处理夏令时（DST）切换——这个时区本身就包含了夏令时和冬令时的切换规则，所以转换后的timestamptz值已经正确包含了时区偏移的变化。

不过你可以用更简洁的内置函数来做同样的事情，效果完全一致：

SELECT to_timestamp(timestamp::integer) AS timestamp FROM table;

to_timestamp()函数接受整数类型的epoch值，直接返回对应的timestamptz，自动应用当前数据库时区的规则。

为什么你查到的时区偏移是3600.0000 #

你执行SELECT EXTRACT(TIMEZONE FROM timestamp) FROM table;得到3600，这说明你查询的那条记录对应的时间处于Europe/Berlin的冬令时期间——冬令时该时区的偏移是UTC+1（也就是3600秒）。如果你找一个处于夏令时的epoch值（比如1620000000，对应UTC时间2021-05-03 15:20:00，此时Europe/Berlin是夏令时，UTC+2），转换后再执行EXTRACT，结果会是7200.0000（2小时=7200秒）。

验证夏令时识别的方法 #

你可以用这条查询来测试：

-- 测试冬令时的epoch（2023-12-20 12:00:00 UTC，对应Europe/Berlin 13:00:00，冬令时UTC+1）
SELECT to_timestamp(1703064000) AS dt, EXTRACT(TIMEZONE FROM to_timestamp(1703064000)) AS tz_offset;

-- 测试夏令时的epoch（2023-06-20 12:00:00 UTC，对应Europe/Berlin 14:00:00，夏令时UTC+2）
SELECT to_timestamp(1687262400) AS dt, EXTRACT(TIMEZONE FROM to_timestamp(1687262400)) AS tz_offset;

执行后你会看到两条结果的时区偏移分别是3600和7200，证明PostgreSQL已经正确识别了夏令时。

可能的优化建议 #

如果你的表中这个列需要经常查询，建议直接修改表结构，把原character varying列转换为timestamptz类型，避免每次查询都做转换：

-- 先添加一个临时timestamptz列
ALTER TABLE your_table ADD COLUMN temp_tz timestamptz;

-- 填充转换后的值
UPDATE your_table SET temp_tz = to_timestamp(original_column::integer);

-- 验证数据无误后，删除原列并重命名临时列
ALTER TABLE your_table DROP COLUMN original_column;
ALTER TABLE your_table RENAME COLUMN temp_tz TO original_column;

这样后续查询就会直接使用正确的带时区时间戳，自动处理夏令时。

总结 #

你的操作本身没有错误，只是可能只检查了冬令时时间段的记录。只要数据库时区设置为Europe/Berlin，转换后的timestamptz值会自动根据时区规则切换夏令时和冬令时的偏移。通过测试不同时间段的epoch值，就能验证这一点。

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