62

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.

CallMeNorm
  • 2,299
  • 1
  • 16
  • 23
  • 2
    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. – Basil Bourque Aug 08 '16 at 16:30
  • 1
    @BasilBourque I've simplified it. – CallMeNorm Aug 08 '16 at 16:35
  • Specify the info/tag about what software you are using to connect/query the PostgreSQL. – Abelisto Aug 08 '16 at 20:01

6 Answers6

87

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"')
CallMeNorm
  • 2,299
  • 1
  • 16
  • 23
  • 5
    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 – smilee89 Dec 19 '17 at 16:31
  • 13
    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"')` – Brugolo Jan 15 '18 at 10:17
  • 3
    To get this to work I needed to cast `now()` to `timestamptz` rather than `timestamp`. – Tim May 25 '18 at 07:45
  • 1
    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) – Mike Jul 06 '22 at 15:27
  • @Mike 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. – ADJenks Aug 09 '22 at 22:35
  • A nice explanation of timezone funkiness is here https://phili.pe/posts/timestamps-and-time-zones-in-postgresql/ and here https://stackoverflow.com/a/21278339/5078765 – ADJenks Aug 09 '22 at 22:37
48

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

ADJenks
  • 2,973
  • 27
  • 38
  • 1
    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'` – Bobby Circle Ciraldo Mar 20 '20 at 20:33
  • 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`. – atoth Apr 13 '22 at 15:45
  • @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. – ADJenks Apr 14 '22 at 20:04
  • @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). – atoth Apr 19 '22 at 19:02
  • How do I get the fractional timezone offset? You said that it only works if the timezone is already a fraction of an hour. – CMCDragonkai Dec 26 '22 at 04:38
  • @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. – ADJenks Dec 27 '22 at 06:46
39

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
Dattaya
  • 879
  • 11
  • 15
7

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.

Hugh
  • 1,171
  • 1
  • 11
  • 15
4

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)
rofrol
  • 14,438
  • 7
  • 79
  • 77
  • 1
    Or using `sql` lang insted of `plpgsql`. `select ret from (select set_config('timezone', tzone, true), to_char(tstamp, mask) ret) t;` https://pastebin.com/EC9KPfZ7 – mpapec Nov 07 '17 at 13:24
1

Simple/trivial:

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

Or if fancier needed use

REGEXP_REPLACE()
LarsTech
  • 80,625
  • 14
  • 153
  • 225
Jay
  • 11
  • 1