Postgres中YYYY-MM-DDTHH:mm:ss.SSSZ时间戳转YYYY-MM-DD HH:mm:ss方法

TEIID31100解析错误和Postgres本身语法无关，是Python连接数据库时走了Teiid中间件代理，该代理的SQL解析器无法识别格式串中的转义字符\"T\"，会把格式字符串里的双引号误判为SQL字符串的结束边界，截断后续SQL语句触发解析报错。此外原写法的to_timestamp格式串未匹配输入值的毫秒、末尾Z时区标识，即使绕过解析器也可能出现时间计算偏差。

注意：Teiid代理环境下不要使用反斜杠转义双引号，会直接触发解析器截断SQL

根据实际连接环境选对应写法即可：

场景1：直连原生Postgres（无Teiid代理） #

Postgres原生兼容ISO8601标准时间格式输入，无需手写复杂格式匹配串，写法最简洁：

• 用于时间范围过滤的查询：

SELECT * FROM my_table 
WHERE my_date >= date_trunc('second', '2022-06-15T08:27:00.599Z'::timestamptz);

• 用于输出格式化为YYYY-MM-DD HH24:MI:SS的时间字符串：

SELECT to_char(
    date_trunc('second', '2022-06-15T08:27:00.599Z'::timestamptz),
    'YYYY-MM-DD HH24:MI:SS'
) AS formatted_time;

场景2：连接经过Teiid代理 #

完全规避特殊转义字符，用基础字符串函数处理输入，绕开Teiid解析器的兼容bug：

• 格式转换直接得到目标输出：

-- 先将T替换为空格，再截取前19位字符，自动丢弃毫秒和Z后缀
SELECT substring(replace('2022-06-15T08:27:00.599Z', 'T', ' ') FROM 1 FOR 19) AS formatted_time;

• 用于时间范围过滤的查询：

SELECT * FROM my_table
WHERE my_date >= substring(replace('2022-06-15T08:27:00.599Z', 'T', ' ') FROM 1 FOR 19)::timestamp;

该写法只用到标准SQL的字符串函数，无特殊转义逻辑，所有兼容ANSI SQL的解析器都可正常执行，不会触发解析报错。

如果需要提前判断输入时间串是否符合指定格式，用正则匹配即可，不依赖时间转换的异常捕获：

SELECT 
  CASE WHEN '2022-06-15T08:27:00.599Z' ~ '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{3}Z$'
  THEN '格式合法'
  ELSE '格式非法'
  END AS validate_result;

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