Snowshoe logo
troubleshooting

Is there an option to parse ISO 8601 dates from json?

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.

Ya

Yannick Koechlin

Asked on Sep 20, 2023

  • The correct format for parsing ISO 8601 dates like 2023-09-20T19:15:07.429861148Z is to use timestamptz (timestamp with time zone) instead of timestamp (timestamp without time zone).
  • The function to_timestamp in PostgreSQL follows a specific template for formatting timestamps. Unfortunately, there is no pattern for nanosecond precision.
  • When dealing with non-UTC timezones, the correct input format should be like 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.
Sep 21, 2023Edited by