PostgreSQL date_trunc指定UTC后月份偏移问题排查

问题描述 #

编写的分区表名称生成函数如下：

CREATE OR REPLACE FUNCTION get_partition_name(table_name VARCHAR, archived_on TIMESTAMP)
RETURNS VARCHAR
AS $get_partition_name$
DECLARE
    partition_date TIMESTAMP := date_trunc('month', archived_on, 'Universal');
BEGIN

    RETURN CONCAT(table_name, to_char(partition_date, '"_y"YYYY"m"MM'));
    
END;
$get_partition_name$ LANGUAGE plpgsql;

调用该函数时传入时间'2024-03-06 12:39:48.399 -0500'，返回结果为george_y2024m02，出现月份偏移一个月的情况；手动将该EST时间转换为UTC并未跨月，但移除函数中的'Universal'参数后结果正常（返回george_y2024m03）。

问题原因 #

核心问题出在参数类型不匹配以及时区处理逻辑上：

1. 参数类型错误：函数的archived_on参数使用了TIMESTAMP类型（无时区时间戳），但你传入的是带时区的时间字符串。PostgreSQL会根据当前会话时区将带时区的字符串转换为无时区的时间戳，丢失了原始的时区信息，导致后续时区相关处理出错。
2. 时区截断逻辑偏差：当使用date_trunc('month', archived_on, 'Universal')时，由于archived_on是无时区时间戳，PostgreSQL会将其视为当前会话时区的时间转换为带时区时间戳，再切换到UTC时区进行截断。如果会话时区与输入时间的时区存在偏移，可能会出现意料之外的月份截断结果（比如你的情况中，会话时区的转换导致UTC截断时落到了上月）。

解决办法 #

将archived_on的参数类型改为TIMESTAMPTZ（带时区时间戳），它能保留输入时间的时区信息，确保时区处理逻辑基于正确的时间值执行。修改后的函数如下：

CREATE OR REPLACE FUNCTION get_partition_name(table_name VARCHAR, archived_on TIMESTAMPTZ)
RETURNS VARCHAR
AS $get_partition_name$
DECLARE
    partition_date TIMESTAMPTZ := date_trunc('month', archived_on, 'UTC');
BEGIN
    RETURN CONCAT(table_name, to_char(partition_date, '_yYYYY"m"MM'));
END;
$get_partition_name$ LANGUAGE plpgsql;

修改后，无论当前会话时区是什么，传入带时区的时间都会被正确转换为UTC时区并截断到当月，返回正确的分区表名称。

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