PostgreSQL中从带时区时间戳列提取epoch的正确语法

你的问题出在转换字符串为时间戳时丢失了时区信息，而非extract(epoch)的语法问题。

错误原因分析 #

你用to_timestamp(ts, 'yyyy-mm-dd h24:mi:ss')转换字符串时，格式模板里没有包含时区部分，PostgreSQL会直接忽略字符串中的+00/-0500时区信息，生成不带时区的timestamp类型值。之后即使你把它转成timestamp with time zone，数据库也会用当前会话的时区去转换这两个原本就丢失了时区信息的时间，最终得到同一个UTC时间，所以Epoch值相同。

正确的处理方式 #

方式1：使用包含时区的格式模板调用to_timestamp

将格式模板补充时区部分，让PostgreSQL正确解析字符串中的时区信息：

select extract(epoch from ts)
from(
    select to_timestamp(ts, 'yyyy-mm-dd hh24:mi:ss.us TZ') ts
    from (
        select unnest(array[
        '1970-01-01 00:00:00.000 +00',
        '1970-01-01 00:00:00.000 -0500'
        ]) ts
    )
);

这里的us用于解析毫秒部分，TZ用于解析时区偏移。

方式2：直接强制转换为timestamp with time zone

PostgreSQL可以自动识别这种带时区的字符串格式，无需手动指定模板：

select extract(epoch from ts::timestamp with time zone)
from(
    select unnest(array[
    '1970-01-01 00:00:00.000 +00',
    '1970-01-01 00:00:00.000 -0500'
    ]) ts
);

方式3：使用to_timestamptz函数（PostgreSQL 12+支持）

这个函数专门用于将字符串转换为带时区的时间戳：

select extract(epoch from ts)
from(
    select to_timestamptz(ts) ts
    from (
        select unnest(array[
        '1970-01-01 00:00:00.000 +00',
        '1970-01-01 00:00:00.000 -0500'
        ]) ts
    )
);

以上三种方式都会正确解析时区信息，返回预期的Epoch值：0和18000.000000。

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