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.

Eyelash

When posting to Stack Overflow, keep your example scenario and code as short and simple as possible. Your complicated query is irrelevant to the question of formatting a date-time. A single line to SELECT the current moment is enough to demonstrate the issue. – Drakensberg

@BasilBourque I've simplified it. – Eyelash

Specify the info/tag about what software you are using to connect/query the PostgreSQL. – Penn

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

Eyelash

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 – Elene

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"') – Glacis

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

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) – Chieftain

@Chieftain You seem to be correct. I never noticed any of these timezone conversion difficulties because I keep my system time at UTC and I always give users UTC and let them convert it themselves. The problems arise when your system time is anything but UTC. If anyone else tried this, use SET TIME ZONE 'America/Toronto'; (or any other non UTC zone) first, or else you might not see the effects. Set it to UTC to see the UTC-to-UTC conversion do nothing. – Timely

A nice explanation of timezone funkiness is here phili.pe/posts/timestamps-and-time-zones-in-postgresql and here https://mcmap.net/q/275848/-postgresql-wrong-converting-from-timestamp-without-time-zone-to-timestamp-with-time-zone – Timely

select to_char(now() at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') is more concise and actually works regardless of the configured timezone. The ::timestamp cast isn't required, @Chieftain 's remark is correct. – Impeach

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

Timely

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' – Libnah

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. – Thermel

@Thermel 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. – Timely

@Timely 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). – Thermel

How do I get the fractional timezone offset? You said that it only works if the timezone is already a fraction of an hour. – Edvard

@CMCDragonkai. I said how immediately after. "e.g. using SET timezone=-4.5;" I.e. if the timezone has a mantessa or fractional part like ".5" in it. This forces "OF" format to display minutes because it's not a whole number. – Timely

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

Synod

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

@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. – Synod

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

Got it, fixed the description, thanks again, @Agiotage – Synod

Great answer for PG9.4+ – Dendroid

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" – Mcdade

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.

Keenakeenan

How to get fractional timezone offsets? Not all timezones is on the hour. – Edvard

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

Deflower

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 – Julijulia

Answer 6

Simple/trivial:

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

Or if fancier needed use

REGEXP_REPLACE()

Ballman

really like how simple this one is if we dont actually care about the T and only want a date formatted string – Philomel