SQLite datetime转换逻辑迁移至PostgreSQL的实现问题

SQLite原实现 #

在SQLite中，使用以下函数将大整数日期转换为本地时间：

datetime(message.date / 1000000000 + strftime("%s", "2001-01-01"), "unixepoch", "localtime")

其中message.date是从1987年起始的大整数（示例值：550535817000000000），strftime("%s", "2001-01-01")返回Unix时间戳978307200。

PostgreSQL尝试过程中的问题 #

尝试在PostgreSQL中复现该逻辑时，遇到了报错和结果错误的情况：

1. 直接相加两个timestamp类型的语句报错：

TO_TIMESTAMP((message.date / 1000000000)) + TO_TIMESTAMP('2001-01-01', 'YYYY-MM-DD HR24:MI:SS')

错误信息：SQL Error [42883]: ERROR: operator does not exist: timestamp with time zone + timestamp with time zone，提示没有匹配的运算符，需要显式类型转换。

2. 单独测试两个部分的结果：

• TO_TIMESTAMP((message.date / 1000000000))输出1987年带时区的日期（示例：1987-05-12 16:36:38.000 -0700）
• TO_TIMESTAMP('2001-01-01', 'YYYY-MM-DD HR24:MI:SS')输出：2001-01-01 00:00:00.000 -0800

正确解决方案 #

基于反馈得到适配PostgreSQL的正确写法（该日期格式对应Messages的chat.db中message表的日期）：

to_timestamp((message.date / 1000000000)::integer + EXTRACT(EPOCH FROM '2001-01-01'::date)::integer) AS message_date,

逻辑说明：先将message.date除以10^9转换为秒级数值，再加上2001-01-01对应的Unix时间戳（通过EXTRACT(EPOCH FROM ...)获取），最后用to_timestamp()转换为带时区的时间格式。

内容的提问来源于stack exchange，提问作者chris.george