When migrating a database from MySQL to PostgreSQL, I bumped into a slight issue with timestamp formatting. PostgreSQL supports many date/time formats, but no native support to output ISO-8601 UTC time & date format (e.g., 2023-08-05T13:54:22Z), favouring consistency with RFC3339 instead.

ISO 8601 specifies the use of uppercase letter T to separate the date and time. PostgreSQL accepts that format on input, but on output it uses a space rather than T, as shown above. This is for readability and for consistency with RFC 3339 as well as some other database systems. https://tools.ietf.org/html/rfc3339

Fortunately, StackOverflow had a solution, including some notes about how to handle timestamps with timezones.

SELECT to_char(now() AT TIME ZONE 'Etc/Zulu', 'yyyy-mm-dd"T"hh24:mi:ss"Z"');

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.