Yannick is looking for a way to parse ISO 8601 dates from JSON in a specific format and encountered some issues with casting timestamps. Xiangjin Wu provided insights on the correct format and datatype to use for parsing ISO 8601 dates.
Yannick Koechlin
Asked on Sep 20, 2023
2023-09-20T19:15:07.429861148Z is to use timestamptz (timestamp with time zone) instead of timestamp (timestamp without time zone).to_timestamp in PostgreSQL follows a specific template for formatting timestamps. Unfortunately, there is no pattern for nanosecond precision.2023-09-20T19:15:07.429861-07:00 with + or - instead of Z, and microsecond precision. The template for this format is YYYY-MM-DDTHH24:MI:SS.US TZH:TZM.