PostgreSQL中如何将带T/Z的时间戳转换为YYYY-MM-DD HH:mm:ss格式

• 转换需求：将格式为2022-06-14T12:47:00.560Z的ISO标准时间字符串，转换为YYYY-MM-DD HH:mm:ss格式，目标输出示例为2022-06-14 12:47:00。

select to_timestamp(to_char('2022-06-14T13:04:00.610Z','YYYY-MM-DD HH24:MI:SS'))
select to_timestamp('2022-06-14T13:04:00.610Z','YYYY-MM-DD HH24:MI:SS')

连接PostgreSQL执行SQL失败，执行语句select to_timestamp(to_char('2022-06-14T13:04:00.610Z','YYYY-MM-DD HH24:MI:SS'))时报错：TEIID30068 The function 'to_char('2022-06-14T13:04:00.610Z', 'YYYY-MM-DD HH24:MI:SS')' is an unknown form. Check that the function name and number of arguments is correct.

连接PostgreSQL执行SQL失败，执行语句select to_timestamp('2022-06-14T13:04:00.610Z','YYYY-MM-DD HH24:MI:SS')时报错：TEIID30068 The function 'to_timestamp('2022-06-14T13:04:00.610Z', 'YYYY-MM-DD HH24:MI:SS')' is an unknown form. Check that the function name and number of arguments is correct.

• to_char函数首参数要求为时间/时间戳类型，直接传入字符串会触发类型不匹配，Teiid层也无法识别该形式的函数调用。
• 传入to_timestamp的格式模板未覆盖原字符串的T分隔符、毫秒段、末尾Z时区标识，模板和待解析字符串结构不匹配，无法正常解析。
• 报错前缀TEIID说明当前SQL执行经过Teiid数据虚拟化层，部分原生PG函数如果未被Teiid兼容，也会抛出函数未知的错误。

方案1：原生PostgreSQL直接强转（PG12及以上版本支持） #

PG12及以上版本原生支持ISO 8601格式字符串直接转为带时区时间戳，再格式化输出即可：

-- 若数据库时区为UTC可直接使用
SELECT TO_CHAR(
  '2022-06-14T13:04:00.610Z'::timestamptz,
  'YYYY-MM-DD HH24:MI:SS'
) AS formatted_time;

如果数据库时区非UTC，需要保留UTC时间的话，先切换时区再转换：

SET TIME ZONE 'UTC';
SELECT TO_CHAR(
  '2022-06-14T13:04:00.610Z'::timestamptz,
  'YYYY-MM-DD HH24:MI:SS'
) AS formatted_time;

方案2：全格式模板匹配写法（兼容低版本PG） #

如果直接强转不被支持，给to_timestamp传入和原字符串完全匹配的格式模板，字面量部分用双引号包裹：

SELECT TO_CHAR(
  TO_TIMESTAMP('2022-06-14T13:04:00.610Z', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'),
  'YYYY-MM-DD HH24:MI:SS'
) AS formatted_time;

模板规则说明：

• "T"/"Z"：双引号包裹的内容按字面量匹配，对应字符串里的T分隔符和末尾Z标识
• MS：匹配字符串中的毫秒部分
  需要严格保留UTC时间的话，加时区转换即可：

SELECT TO_CHAR(
  TO_TIMESTAMP('2022-06-14T13:04:00.610Z', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AT TIME ZONE 'UTC',
  'YYYY-MM-DD HH24:MI:SS'
) AS formatted_time;

方案3：字符串处理写法（兼容Teiid层，无函数兼容问题） #

如果Teiid层不识别时间转换类函数，直接通过字符串替换+截断实现，不需要做时间类型转换，兼容性最高：

SELECT SUBSTRING(REPLACE('2022-06-14T13:04:00.610Z', 'T', ' '), 1, 19) AS formatted_time;

以上三种写法的输出均为目标值2022-06-14 13:04:00。

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