如何拼接本地时间与时区并插入带时区的timestamp列？

问题原因分析 #

你之前拼接时间字符串与时区偏移量的方法无效，是因为PostgreSQL将拼接后的字符串转为TIMESTAMP WITH TIME ZONE时，会自动把带偏移的时间转换为UTC存储，查询时若客户端时区为UTC，就会显示UTC时间，和你预期的原本地时间加偏移的结果不符。

正确解决方案 #

利用PostgreSQL内置的AT TIME ZONE运算符，直接将本地时间（TIMESTAMP WITHOUT TIME ZONE类型）与对应时区关联，转换为TIMESTAMP WITH TIME ZONE类型，无需手动拼接偏移量。

假设你的表名为your_table，本地时间列是local_ts，时区列是tz（存储如Europe/Berlin这样的时区名称），操作步骤如下：

1. 新增带时区的列

ALTER TABLE your_table ADD COLUMN ts_with_tz TIMESTAMP WITH TIME ZONE;

2. 转换并填充数据

UPDATE your_table SET ts_with_tz = local_ts AT TIME ZONE tz;

原理说明 #

local_ts AT TIME ZONE tz的作用是：将local_ts的值视为tz时区的本地时间，转换为对应的TIMESTAMP WITH TIME ZONE类型（PostgreSQL内部以UTC格式存储该类型数据）。当你查询该列时，若客户端时区设置为tz列对应的时区（比如Europe/Berlin），就会显示为你想要的格式，例如2021-02-01 12:21:51.000000 +01:00。

固定格式查询（可选） #

如果需要在查询时强制显示为原时区的格式（不受客户端时区影响），可以使用TO_CHAR函数格式化输出（注意此时结果为字符串类型，而非TIMESTAMP WITH TIME ZONE）：

SELECT TO_CHAR(ts_with_tz AT TIME ZONE tz, 'YYYY-MM-DD HH24:MI:SS.US TZ') AS formatted_ts
FROM your_table;

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