PostgreSQL中字符串转TIMESTAMP及NULL值处理方法

你遇到的Failed to tokenize string [Y]错误和NULL值无关，核心原因是格式字符串的转义写法错误——你使用了HTML转义的"，这在SQL中会被当作普通字符处理，导致数据库无法正确解析日期格式。

正确的解决方案： #

1. 修正格式字符串写法

针对你的日期格式2024-03-27T11:27:20.215419+00:00，直接在格式字符串中用双引号包裹固定的T字符即可，不需要HTML转义：

SELECT
    id,
    cancel_date,
    TO_TIMESTAMP(cancel_date, 'YYYY-MM-DD"T"HH24:MI:SS.US+TZ') AS formatted_timestamp
FROM mytable;

甚至更简单：这种ISO 8601标准格式，PostgreSQL可以自动识别，无需指定格式字符串：

SELECT
    id,
    cancel_date,
    TO_TIMESTAMP(cancel_date) AS formatted_timestamp
FROM mytable;

2. 关于NULL值的处理

TO_TIMESTAMP函数本身会在输入为NULL时返回NULL，完全不需要额外的CASE WHEN判断。如果你的字段中存在非NULL但格式错误的脏数据，可以用PostgreSQL 12+支持的TRY_TO_TIMESTAMP函数，它会在转换失败时返回NULL，避免报错：

SELECT
    id,
    cancel_date,
    TRY_TO_TIMESTAMP(cancel_date) AS formatted_timestamp
FROM mytable;

3. 若需截断毫秒后转换（修正你的尝试代码）

如果你一定要截断毫秒部分再转换，只需把格式字符串里的"替换为直接的双引号即可：

SELECT
    id,
    cancel_date,
    SUBSTRING(cancel_date FROM 1 FOR 19),
    CASE 
        WHEN cancel_date IS NOT NULL THEN
            TO_TIMESTAMP(SUBSTRING(cancel_date FROM 1 FOR 19), 'YYYY-MM-DD"T"HH24:MI:SS')
        ELSE NULL 
    END AS formatted_timestamp
FROM mytable;

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