如何修改PostgreSQL中TIMESTAMPTZ值的部分属性？

核心结论 #

不行，必须单独存储用户的原始IANA时区，才能避免你提到的编辑不一致问题。

原因分析 #

PostgreSQL的TIMESTAMPTZ类型确实只存储UTC绝对时间戳，不会保留输入时的原始时区信息。当你插入带时区的时间时，数据库会自动将其转换为UTC值存储，原始时区的上下文会丢失。

如果不存储原始时区，修改月份、日期时只能基于UTC时间操作，这会和用户预期产生偏差：

• 比如用户最初在America/New_York时区输入2024-11-01 10:00:00（对应UTC15:00:00），想把日期改成12月1日的同一本地时间。如果直接基于UTC修改，遇到夏令时切换的边界日期时，得到的本地时间小时数可能发生变化，完全不符合用户预期。
• 再比如用户想将月份改成2月，基于UTC的2月天数计算和基于用户本地时区的计算可能出现日期偏移（比如UTC的2月最后一天对应本地时区的3月1日）。

解决方案 #

在表中新增一列存储用户的原始IANA时区（比如user_timezone，类型为text，值如America/New_York），修改时按以下步骤操作：

1. 将TIMESTAMPTZ值转换为用户原始时区的本地时间（TIMESTAMP类型）。
2. 在本地时间的上下文下修改月份、日期等字段。
3. 将修改后的本地时间转换为目标时区的TIMESTAMPTZ值，同时更新存储的时区字段。

示例代码 #

假设表结构：

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMPTZ NOT NULL,
    user_tz TEXT NOT NULL -- 存储用户原始IANA时区
);

修改月份为4月、时区改为Europe/London的SQL：

UPDATE events
SET
    event_time = (
        -- 转换为用户原始时区的本地时间
        (event_time AT TIME ZONE user_tz)
        -- 调整日期：将月份改为4月，保留原年、日、时间
        ::DATE + INTERVAL '1 month' + EXTRACT(TIME FROM (event_time AT TIME ZONE user_tz))::INTERVAL
    ) AT TIME ZONE 'Europe/London', -- 转换为新时区的TIMESTAMPTZ
    user_tz = 'Europe/London' -- 更新存储的时区
WHERE id = 1;

或者更精准的日期构造：

UPDATE events
SET
    event_time = (
        make_date(
            EXTRACT(YEAR FROM (event_time AT TIME ZONE user_tz))::INT,
            4, -- 目标月份
            EXTRACT(DAY FROM (event_time AT TIME ZONE user_tz))::INT
        ) + EXTRACT(TIME FROM (event_time AT TIME ZONE user_tz))::INTERVAL
    ) AT TIME ZONE 'Europe/London',
    user_tz = 'Europe/London'
WHERE id = 1;

这样操作就能确保修改是基于用户预期的本地时间上下文，避免UTC转换带来的不一致问题。

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