如何修复PostgreSQL数据库中错误的日期时间与时区数据

问题背景 #

向PostgreSQL的timestamptz类型列插入数据时出现时区相关异常，示例错误数据为2023-10-24 00:00:00.000 +0100，该数据由无时区的日期生成，默认采用BST时区。

排查过程 #

最初误以为是DBeaver默认Europe/London时区的转换问题，实际定位为客户端应用逻辑异常：时序数据的日期列要求统一为UTC午夜00:00，但部分数据偏移1小时，导致数据比对出错。已修复客户端代码，需同步修正数据库中已存在的错误数据——将非+00:00时区的行调整为UTC时区，且保持时间为00:00。

尝试使用at time zone 'UTC'未解决问题，将PostgreSQL与DBeaver时区设为UTC后，错误数据显示为2024-05-05 23:00:00.000 +0000，目标应为2024-05-06 00:00:00.000 +0000。

最终修复步骤 #

1. 设置会话时区为UTC #

先确认并切换时区，避免时区转换干扰后续操作：

select CURRENT_TIME;
show timezone;
set timezone = 'utc';
select CURRENT_TIME;
show timezone;

2. 添加临时校正列 #

新增一个timestamptz类型的列，用于存储校正后的数据：

alter table my_table
add column price_state_date_new timestamptz;

3. 校正错误数据 #

针对UTC时区下显示为23:00的错误数据，添加1小时偏移，将其调整为次日00:00 UTC：

update
    my_table
set
    price_state_date_new = price_state_date + interval '1 hour'
where
    extract(hour from price_state_date) = 23
and
    extract(minute from price_state_date) = 0;

4. 验证校正结果 #

执行以下SQL确认所有数据的时间部分均为00:00:00：

-- 检查时间部分非00:00:00的行（正常应无返回结果）
select
    price_state_date,
    price_state_date_new,
    cast(cast(price_state_date_new as time) as varchar) as price_state_date_time_varchar
from
    my_table
where
    cast(cast(price_state_date_new as time) as varchar) not like '00:00:00';

-- 检查时分秒不全为0的行（正常应无返回结果）
select
    *
from
    my_table
where
    extract(hour from price_state_date_new) != 0
or
    extract(minute from price_state_date_new) != 0
or
    extract(second from price_state_date_new) != 0;

-- 分组查看原数据与校正后数据的对应关系
select
    count(*),
    price_state_date,
    price_state_date_new
from
    my_table
group by
    price_state_date,
    price_state_date_new
order by
    price_state_date desc,
    price_state_date_new desc;

5. 替换原列 #

确认校正无误后，替换原列完成修复：

-- 确保新列非空
alter table
    my_table
alter column
    price_state_date_new set not null;

-- 删除原错误列
alter table
    my_table
drop
    price_state_date;

-- 将新列重命名为原列名
alter table
    my_table
rename column
    price_state_date_new to price_state_date;

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