PostgreSQL视图JSONB时间字段转换不准确问题求解

问题根源 #

你的代码存在两个核心问题：

1. 格式串不匹配：JSONB中的时间是标准ISO 8601格式（2022-06-27T18:32:36Z），但你用to_timestamp指定的格式串YYYY-MM-DD HH24:MI:SS没有匹配其中的T分隔符和Z时区标识，导致解析错误。
2. 时区处理冗余：原代码的时区转换逻辑绕了不必要的弯路，且Europe不是PostgreSQL认可的有效时区（需要具体到如Europe/Paris、Europe/London这类时区名称）。

修正后的转换方案 #

PostgreSQL原生支持解析带Z（UTC标识）的ISO 8601时间字符串，无需手动指定格式串，直接转换为带时区的时间戳（timestamptz）即可，再按需转换为目标格式或时区。

完整SQL示例

-- 处理Time字段（假设JSONB中Time为纯时间字符串，如"18:32:36"）
(checklist.record ->> 'Time')::time without time zone AS "time",

-- 处理Modified字段：转为UTC时区的日期时间（timestamp without time zone）
(checklist.record ->> 'Modified')::timestamptz AT TIME ZONE 'UTC' AS modified_utc,

-- 处理Modified字段：转为欧洲某具体时区的日期时间（替换为你实际需要的时区）
(checklist.record ->> 'Modified')::timestamptz AT TIME ZONE 'Europe/Paris' AS modified_europe,

-- 处理Created字段：同理转换为UTC时区
(checklist.record ->> 'Created')::timestamptz AT TIME ZONE 'UTC' AS created_utc,

-- 若需单独提取日期或时间部分
((checklist.record ->> 'Created')::timestamptz AT TIME ZONE 'UTC')::date AS created_date,
((checklist.record ->> 'Created')::timestamptz AT TIME ZONE 'UTC')::time AS created_time

关键说明 #

• ::timestamptz：自动识别Z为UTC时区，将字符串转换为带时区的时间戳，这是最可靠的解析方式。
• AT TIME ZONE '时区'：将带时区的时间戳转换为指定时区的本地时间（timestamp without time zone），请确保使用PostgreSQL支持的具体时区名称（可通过SELECT * FROM pg_timezone_names;查看所有有效时区）。
• 若JSONB中的Time字段也是完整ISO格式，同样可以用(checklist.record ->> 'Time')::timestamptz::time来提取时间部分。

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