Turn postgres date representation into ISO 8601 string

Question

I'm trying to format a Postgres date representation into a ISO 8601 string. I'm assuming that there is a Postgres function that can do it, but I found the documentation short on examples.

My query is

SELECT   now()::timestamp 

which returns

[{{2016, 8, 9}, {3, 56, 55, 754181}}] 

I'm trying to get the date into a format that looks more like 2016-8-9T03:56:55+00:00.

What changes do I need to make to my query to make that happen? Thanks for your help.

Answer 1

I think I found a way to do the formatting, but it's not ideal because I'm writing the formatting myself.

Here is a potential solution:

SELECT to_char (now()::timestamp at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') 

Answer 2

This is a terse way to "turn a PostgreSQL date representation into an ISO 8601 string":

SELECT to_json(now())#>>'{}' 

It uses the #>> operator in combination with the to_json() function, which can both be found on this page: https://www.postgresql.org/docs/current/functions-json.html

The operator "Get[s] JSON object at specified path as text". However when you specify an empty array literal '{}' as the path, it specifies the root object.

Compare this method to similar methods:

SELECT to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF') AS most_lengthy, -- See note: * trim(both '"' from to_json(now())::text) AS a_bit_lengthy, to_json(now())::text AS unwanted_quotes, to_json(now())#>>'{}' AS just_right 

It's shorter but produces the same results.

* Also, JavaScript will not parse the first method's output via the Date() constructor, because it expects a simplification of the ISO 8601 which only accepts time zones in (+/-)HH:mm or Z format, but OF returns (+/-)HH format without the minutes, UNLESS the input timezone is a fraction of an hour, e.g. using SET timezone=-4.5; at the beginning of the session. Alternatively you could manually append your timezone as a string to the lengthy version and exclude the OF