PostgreSQL FDW视图中AGE函数计算时区偏移问题及解决需求

问题原因 #

视图中使用AGE(current_timestamp, birth_dtm)计算时，birth_dtm是TIMESTAMP类型（无时区信息）：

• 本地查询时，PostgreSQL会将birth_dtm视为当前会话时区（Asia/Kolkata）的时间，与current_timestamp（带时区）计算出正确的年龄差
• 通过postgresql_FDW访问时，远程会话的时区被强制设为UTC，birth_dtm会被当作UTC时间处理，导致计算结果与本地直接计算的差值等于当前时区与UTC的偏移量（此处为5小时30分）

解决方案 #

方案1：修改视图，显式指定birth_dtm的时区 #

在视图中明确将birth_dtm解析为目标时区的时间，再进行AGE计算，确保无论远程时区如何设置，计算逻辑一致：

CREATE OR REPLACE VIEW view_test AS
    SELECT  id, 
            birth_dtm, 
            AGE(current_timestamp, birth_dtm AT TIME ZONE 'Asia/Kolkata') c_age
    FROM    test;

• birth_dtm AT TIME ZONE 'Asia/Kolkata'会将无时区的TIMESTAMP转换为带时区的TIMESTAMPTZ，确保与current_timestamp的时区逻辑匹配

方案2：配置FDW远程会话时区 #

在创建或修改foreign server时，指定远程会话的时区与本地一致，让视图在远程执行时使用相同的时区计算：

-- 创建foreign server时指定时区
CREATE SERVER FDB
FOREIGN DATA WRAPPER postgresql_fdw
OPTIONS (
    host '你的远程主机地址', 
    port '5432', 
    dbname '远程数据库名', 
    options '-c timezone=Asia/Kolkata'
);

-- 如果已存在foreign server，使用ALTER修改
ALTER SERVER FDB
OPTIONS SET options '-c timezone=Asia/Kolkata';

• 此方法会让远程PostgreSQL会话使用Asia/Kolkata时区，视图中的current_timestamp和birth_dtm的处理逻辑与本地完全一致

方案3：修改表结构为带时区的时间类型 #

将birth_dtm字段改为TIMESTAMPTZ（带时区），从根源上避免时区歧义：

-- 修改字段类型（注意：若已有数据，会自动按当前时区转换）
ALTER TABLE test ALTER COLUMN birth_dtm TYPE TIMESTAMPTZ;

-- 重新创建视图（无需额外时区处理）
CREATE OR REPLACE VIEW view_test AS
    SELECT  id, birth_dtm, AGE(current_timestamp, birth_dtm) c_age
    FROM    test;

• 此方法需要修改表结构，适合允许调整表定义的场景，后续所有跨时区的时间计算都会自动适配

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