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.

Solution 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"')

Solution 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

Solution 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

Solution 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.

Solution 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

View more solutions

Comments

[cdmckay]

to_json(now()) doesn't have the T in it in Postgres 9.3

[Dattaya]

@cdmckay, thanks for pointing that out, I added to the answer: "since 9.6" though I haven't tried to run the query in 9.5 and 9.4.

[cdmckay]

it's fine in 9.4 and 9.5, only 9.3 has the T weirdness

[Dattaya]

Got it, fixed the description, thanks again, @cdmckay

[mpapec]

Or using sql lang insted of plpgsql. select ret from (select set_config('timezone', tzone, true), to_char(tstamp, mask) ret) t; pastebin.com/EC9KPfZ7

[smilee89]

this form does not work, at least in PG10. ::timestamp removes timezoneinformation and 'at timezone UTC' shifts the offset again, so you shift it twice. (now() at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'); does work

[Brugolo]

In my case I need as well the milliseconds so you just need to add .MS after the seconds. to_char (now()::timestamp at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')

[Tim]

To get this to work I needed to cast now() to timestamptz rather than timestamp.

[danius]

Great answer for PG9.4+

[Bobby Circle Ciraldo]

Thanks so much. For my own purposes I am dealing with a "TIMESTAMP WITHOUT TIMEZONE" type, which I want to force to UTC, so I augmented this solution by appending the 'Z' char like this: to_json(my_column)#>>'{}' || 'Z'

[Zenwalker]

I am on PG 11 and it seems to working.. select to_json(CURRENT_TIMESTAMP), CURRENT_TIMESTAMP output: """2021-06-11T12:01:25.981481+05:30""" "2021-06-11 12:01:25.981481+05:30"

[atoth]

In my own tests trailing zeros are removed with this solution: 2022-03-31 17:39:23.500 => 2022-03-31T17:39:23.5Z. Note the missing 0 before the Z.

[ADJenks]

@atoth the trailing zeros are removed when I simply create the timestamp and convert it back to text. Therefore I don't think postgres stores that precision in a timestamp before it is converted. For example, SELECT '2022-03-31 17:39:23.500'::timestamp::text outputs 2022-03-31 17:39:23.5. That's a good point though. If you need a specific length of precision, you'll have to use a more explicit format to pad the ending. Thanks for pointing that out. Added some details to my answer for you.

[atoth]

@ADJenks In our case we had a check for a specific format that actually failed without padding zeros. Now the philosophical question arises that should we be that detailed about the format or should we only care if JS is able to parse the date (or your entire data pipeline at your company).

[Mike]

This answer is wrong and dangerous. This only works if your timezone is already UTC. Casting timestamptz to timestamp causes the timezone offset to be dropped. at time zone then sets the zimezone offset to 0. You are NOT converting the timestamptz to UTC. Instead you just overriding the timezone offset. select now(), now() at time zone 'UTC', now()::timestamp at time zone 'UTC'; 2022-07-06 10:26:11.282728-05 | 2022-07-06 15:26:11.282728 (correct UTC) | 2022-07-06 05:26:11.282728-05 (incorrect)