如何在PostgreSQL中存储时间戳的原始时区偏移？

PostgreSQL的TIMESTAMP WITH TIME ZONE（简称timestamptz）确实只会将时间转换为UTC存储，原始输入的时区偏移信息会丢失，所以要保留原始偏移，必须额外存储相关信息，以下是几种实用方案：

方案1：新增偏移列，用内置函数生成原始格式字符串 #

这是你设想方案的优化版，不用手动拼接字符串，利用PostgreSQL的日期函数简化操作：

1.1 修改表结构 #

新增一列存储原始时区偏移（建议用TEXT类型存储+09:00这种标准格式，或者用INTERVAL类型）：

CREATE TABLE what_happened (
  id SERIAL PRIMARY KEY,
  action VARCHAR(128),
  created TIMESTAMP WITH TIME ZONE NOT NULL,
  original_tz_offset TEXT NOT NULL -- 存储如'+09:00'的偏移字符串
);

1.2 插入数据 #

插入时同时保存timestamptz值和原始偏移，比如：

INSERT INTO what_happened (action, created, original_tz_offset)
VALUES ('test', '2023-06-30T12:34:56+0900', '+09:00');

1.3 查询原始格式 #

用AT TIME ZONE将UTC时间转换回原始偏移的本地时间，再通过to_char格式化后拼接偏移，得到符合ISO 8601的原始格式：

SELECT 
  id,
  action,
  to_char(created AT TIME ZONE original_tz_offset, 'YYYY-MM-DD"T"HH24:MI:SS') || original_tz_offset AS created_with_original_tz
FROM what_happened;

执行后会返回2023-06-30T12:34:56+09:00这样的原始格式。

方案2：存储原始时间字符串（搭配timestamptz列） #

如果不想做任何转换操作，可以直接新增一列存储原始的带偏移时间字符串，同时保留timestamptz列用于时间计算：

2.1 修改表结构 #

CREATE TABLE what_happened (
  id SERIAL PRIMARY KEY,
  action VARCHAR(128),
  created TIMESTAMP WITH TIME ZONE NOT NULL,
  created_original TEXT NOT NULL -- 存储原始输入的带偏移字符串
);

2.2 插入与查询 #

插入时直接把原始时间字符串存到created_original：

INSERT INTO what_happened (action, created, created_original)
VALUES ('test', '2023-06-30T12:34:56+0900', '2023-06-30T12:34:56+0900');

查询时直接取created_original即可，无需任何转换，适合只需要展示原始时间、不需要二次计算的场景。

方案3：存储时区名称（更严谨的时区规则支持） #

如果你的场景需要考虑时区规则变化（比如夏令时），存储固定偏移可能不够准确，这时可以存储时区名称（如Asia/Tokyo、Europe/Berlin）：

3.1 修改表结构 #

CREATE TABLE what_happened (
  id SERIAL PRIMARY KEY,
  action VARCHAR(128),
  created TIMESTAMP WITH TIME ZONE NOT NULL,
  original_tz TEXT NOT NULL -- 存储时区名称
);

3.2 插入与查询 #

插入时存储对应的时区名称：

INSERT INTO what_happened (action, created, original_tz)
VALUES ('test', '2023-06-30T12:34:56+0900', 'Asia/Tokyo');

查询时转换为该时区的本地时间并格式化：

SELECT 
  id,
  action,
  to_char(created AT TIME ZONE original_tz, 'YYYY-MM-DD"T"HH24:MI:SSOF') AS created_with_original_tz
FROM what_happened;

这里OF格式符会自动生成对应时区的偏移（如+0900），确保输出符合ISO 8601标准。

关键说明 #

PostgreSQL没有内置类型可以同时保存UTC时间和原始时区偏移，所以额外存储是唯一的解决方案。选择哪种方案取决于你的需求：

• 若只需要固定偏移，选方案1；
• 若只需要展示原始时间，选方案2；
• 若需要严格遵循时区规则（含夏令时），选方案3。

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