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.

User @atoth pointed out that the subsecond component has its trailing zeros removed using to_json() so 2022-03-31 17:39:23.500 is converted to 2022-03-31T17:39:23.5Z. Since some date recipients require very specific format, I tested the following:

SELECT
to_char('2022-03-31 17:39:23.5'::timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.MSOF'),
to_char('2022-03-31 17:39:23.500'::timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.MSOF'),
to_char('2022-03-31 17:39:23.5123456789'::timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.MSOF'),
to_char('2022-03-31 17:39:23.5123456789'::timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.USOF')

This was the output from Postgres 10:

"2022-03-31T17:39:23.500+00", "2022-03-31T17:39:23.500+00", "2022-03-31T17:39:23.512+00", "2022-03-31T17:39:23.512346+00"

So it appears that with MS you can specify exactly 3 decimals, no more and no less and with US you will get exactly 6, no more and no less. Outside of these two precision options, you'll have to do some funky string manipulation.

Have fun!

* 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

Answer 3

Maybe for someone it would be helpful to know that since Postgres 9.4 to_json function (as well as row_to_json) also converts timestamp to a proper ISO 8601 format but in addition it wraps a value in quotes which might not be desirable:

SELECT now();
  2017-05-10 15:57:23.736054+03

SELECT to_json(now());
  "2017-05-10T15:57:23.769561+03:00"

-- in case you want to trim the quotes
SELECT trim(both '"' from to_json(now())::text);
  2017-05-10T15:57:23.806563+03:00

Answer 4

Set the timezone session variable to whatever timezone you want the output to be in, then use to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF')

If you use at time zone '...' be aware that this will strip off any timezone information, and assume that the user already knows the timezone.

If you use at time zone 'UTC' then the output should always be the UTC time, with correct time zone information (no offset).

set timezone='UTC';


select to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF');

2017-11-17T02:02:26+00  /* UTC time */


select to_char(now() at time zone 'Australia/Sydney', 'YYYY-MM-DD"T"HH24:MI:SSOF');

2017-11-17T13:02:26+00  /* Local Sydney time, but note timezone is incorrect. */


set timezone='Australia/Sydney';


select to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF');

2017-11-17T13:02:26+11  /* Local Sydney time with correct time zone! */


select to_char(now() at time zone 'Australia/Sydney', 'YYYY-MM-DD"T"HH24:MI:SSOF');

2017-11-17T13:02:26+00  /* Still local Sydney time, but time zone info has been removed. */


select to_char(now() at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSOF');

2017-11-17T02:02:26+00  /* Correct UTC time with correct offset. */

This blog post gives quite a detailed explanation.

Answer 5

Only function worked for me because you need to set timezone.

To have default value timezone with zone:

create table somedata (
  release_date timestamptz DEFAULT NOW()
)

Create function:

CREATE OR REPLACE FUNCTION date_display_tz(param_dt timestamp with time zone)
 RETURNS text AS
$$
DECLARE var_result varchar;
BEGIN
PERFORM set_config('timezone', 'UTC', true);
var_result := to_char(param_dt , 'YYYY-MM-DD"T"HH24:MI:SS:MS"Z"');
RETURN var_result;
END;
$$ language plpgsql VOLATILE;

And output:

# SELECT
#   localtimestamp, current_timestamp,
#   to_char(localtimestamp, 'YYYY-MM-DD"T"HH24:MI:SS:MS"Z"'),
#   to_char(current_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS:MS"Z"'),
#   date_display_tz(localtimestamp), date_display_tz(current_timestamp);
         timestamp          |              now              |         to_char          |         to_char          |     date_display_tz      |     date_display_tz
----------------------------+-------------------------------+--------------------------+--------------------------+--------------------------+--------------------------
 2017-04-27 23:48:03.802764 | 2017-04-27 21:48:03.802764+00 | 2017-04-27T23:48:03:802Z | 2017-04-27T23:48:03:802Z | 2017-04-27T21:48:03:802Z | 2017-04-27T21:48:03:802Z
(1 row)

Look at this also:

If you want the server to return time zone information respective of another time zone, I believe you'll need to use SET TIME ZONE. Otherwise, the server automatically (converts the timestamp) and returns the time zone of the server.

test=# select (current_timestamp at time zone 'UTC') at time zone 'UTC';
            timezone
-------------------------------
  2005-04-22 16:26:57.209082+09
(1 row)

test=# set time zone 'UTC';
SET
test=# select (current_timestamp at time zone 'UTC') at time zone 'UTC';
            timezone
-------------------------------
  2005-04-22 07:27:55.841596+00
(1 row)

test=# select (current_timestamp at time zone 'UTC');
           timezone
----------------------------
  2005-04-22 07:28:48.888154
(1 row)

test=# select (current_timestamp at time zone 'UTC')::timestamptz;
            timezone
-------------------------------
  2005-04-22 07:38:19.979511+00
(1 row)

• http://sql-info.de/postgresql/notes/to_date-to_timestamp-gotchas.html

Answer 6

Simple/trivial:

SELECT REPLACE(NOW()::TEXT, ' ', 'T');

Or if fancier needed use

REGEXP_REPLACE()