PostgreSQL按UTC小时分区插入数据却落入本地时区分区求助

问题背景 #

尝试创建基于UTC小时的分区表，但插入记录时，数据落入了基于本地时区（EST）的分区。表的分区字段为毫秒级Epoch大整数audio_file_start_unix_millis，通过pg_partman工具创建回溯45天、超前28天的小时分区，分区创建正常，但数据未按UTC时区落入预期分区。

表结构与分区创建代码 #

create table voice.voicetext
(
    audio_file_start_unix_millis    bigint not null,
    facility                        varchar(4) not null,
    dalr_channel                    int not null,
    audio_file_end_unix_millis      bigint not null,
    processed_timestamp_unix_millis bigint not null,
    audio_file                      varchar(150) not null,
    segment_uuid                    varchar(50) not null,
    speaker_role                    varchar(20) not null,
    audio_file_uuid                 varchar(50) not null,
    diarization_confidence          numeric(9,8) not null,
    segment_start_unix_millis       bigint not null,
    segment_end_unix_millis         bigint not null,
    transcription_uuid              varchar(50) not null,
    transcription_confidence        numeric(19,18) not null,
    transcription_text              text not null,
    control_position                varchar(10),
    speaker_gender                  int,
    adddate                         timestamp default current_timestamp,
    addwho                          varchar(50) default current_user,
    editedate                       timestamp default current_timestamp,
    editwho                         varchar(50) default current_user,
    constraint un_voicetext unique (audio_file_start_unix_millis,facility,dalr_channel,control_position)
) PARTITION BY RANGE (audio_file_start_unix_millis);

create index idx_voicetext_start_timestamp on voice.voicetext( timezone('Etc/UTC',to_timestamp(audio_file_start_unix_millis::decimal/1000)));

SELECT partman.create_parent( p_parent_table => 'voice.voicetext',
                p_control => 'audio_file_start_unix_millis',
                p_type => 'native',
                p_epoch => 'milliseconds',
                p_interval=> 'hourly',
                p_premake => (24*28),
                p_start_partition =>  date_trunc('day', (current_timestamp at time zone 'utc') - interval '45 day')::varchar
);

问题示例 #

插入一条数据，其audio_file_start_unix_millis值为1727740793391，该时间戳转换为：

• UTC时间：2024年9月30日 23:59:53.391
• EST时间：2024年9月30日 19:59:53.391（GMT-04:00 DST）

期望数据落入voice.voicetext_p2024_09_30_2300分区，但实际落入了voice.voicetext_p2024_09_30_1900分区：

insert into voice.voicetext(audio_file_start_unix_millis
,facility
,dalr_channel
,audio_file_end_unix_millis
,processed_timestamp_unix_millis
,audio_file
,segment_uuid
,speaker_role
,audio_file_uuid
,diarization_confidence
,segment_start_unix_millis
,segment_end_unix_millis
,transcription_uuid
,transcription_confidence
,transcription_text
,control_position
,speaker_gender)
values( 1727740793391
,'XXX'
, 21090
, 1727740810784
, 1728541141116
, 's3://bucket/file.txt'
, 'xxxxxxxxxxxx-xxxxxxxxxxxxxx'
, 'controller'
, 'filename'
, 0.992303
, 1727740798431
, 1727740801451
, 'xxxxxxxx-xxxxxxxxx-xxxx'
, 0.7172371354862196
, 'does not matter'
, 'XX'
, null);

select audio_file_start_unix_millis from voice.voicetext_p2024_09_30_1900;
-- 结果：
-- audio_file_start_unix_millis|
-- ----------------------------+
--                1727740793391|

问题原因 #

pg_partman默认使用数据库的本地时区转换Epoch时间戳，以此生成分区名称并计算分区范围。由于你的数据库时区为EST，分区命名和范围计算都基于EST，导致UTC时间对应的分区名称显示为EST时间，造成误解。

解决方案 #

在调用partman.create_parent时，显式指定p_timezone参数为'UTC'，让pg_partman使用UTC时区生成分区名称和计算分区范围：

SELECT partman.create_parent( 
    p_parent_table => 'voice.voicetext',
    p_control => 'audio_file_start_unix_millis',
    p_type => 'native',
    p_epoch => 'milliseconds',
    p_interval=> 'hourly',
    p_premake => (24*28),
    p_start_partition =>  date_trunc('day', (current_timestamp at time zone 'utc') - interval '45 day')::varchar,
    p_timezone => 'UTC' -- 新增参数指定UTC时区
);

后续操作 #

1. 若已创建错误时区的分区，需先备份数据后删除现有分区，再执行上述修正后的create_parent语句。
2. 插入数据时，确保audio_file_start_unix_millis为正确的UTC毫秒级Epoch时间戳。

验证 #

重新创建分区后，插入测试数据，查询voice.voicetext_p2024_09_30_2300分区，数据会正确落入该分区。

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