PostgreSQL 12中如何将ISO格式日期字符串转换为int8类型

正确转换方案 #

方案1：直接使用PostgreSQL原生ISO8601格式识别（最简写法） #

PostgreSQL原生支持带Z后缀的ISO8601格式字符串直接转换为TIMESTAMPTZ类型，无需手动指定格式串：

-- 转换为秒级Unix时间戳（int8类型，常规需求首选）
SELECT EXTRACT(EPOCH FROM '2021-10-18T17:45:22Z'::TIMESTAMPTZ)::INT8;

-- 转换为毫秒级Unix时间戳（int8类型，按需使用）
SELECT (EXTRACT(EPOCH FROM '2021-10-18T17:45:22Z'::TIMESTAMPTZ) * 1000)::INT8;

方案2：显式指定格式串（兼容性更强） #

如果需要严格控制格式匹配，手动指定格式串时需要把固定字符T、Z用双引号包裹，同时使用HH24匹配24小时制的小时：

SELECT EXTRACT(EPOCH FROM TO_TIMESTAMP('2021-10-18T17:45:22Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'))::INT8;

特殊需求：转换为年月日时分秒拼接的整数 #

如果你需要的不是Unix时间戳，而是20211018174522这种按时间数字拼接的int8，可以使用以下写法：

SELECT TO_CHAR('2021-10-18T17:45:22Z'::TIMESTAMPTZ, 'YYYYMMDDHH24MISS')::INT8;

原有写法报错原因 #

1. 第一个写法错误点：
   • 格式串未匹配输入中的固定字符T和Z，末尾多了冗余空格
   • 小时占位符用了hh（对应12小时制），和输入的24小时制时间不匹配
2. 第二个写法错误点：
   • to_char第一个参数要求传入日期/时间类型，直接传入字符串会触发类型错误
   • 逻辑方向错误：即便类型匹配，转成格式化字符串再转int得到的是拼接数字，不是常规需求的Unix时间戳

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