Rotate postgres date representation to ISO 8601 string

Question

I am trying to format the representation of a Postgres date to an ISO 8601 string. I assume there is a Postgres function that can do this, but I found the documentation with examples.

My request

SELECT now()::timestamp 

which returns

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

I am trying to get a date in a format that looks more like 2016-8-9T03:56:55+00:00 .

What changes need to be made to my request for this to happen? Thank you for your help.

Answer 1

I think I found a way to do the formatting, but this is not ideal, because I write 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

Perhaps it would be useful for someone to know that since the Postgres 9.4 to_json (as well as row_to_json ) also converts the timestamp to the appropriate ISO 8601 format, but it also puts the value in quotation marks, which may 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 3

Only the function worked for me because you need to set the time zone.

To set the default time zone with a 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 the conclusion:

 # 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) 

Check this out :

If you want the server to return time zone information corresponding to a different time zone, I believe you 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 4

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

If you use at time zone '...' , remember that this will disable any time zone information and assume that the user already knows the time zone.

If you use at time zone 'UTC' , then the output should always be UTC time with the 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 provides a fairly detailed explanation.

Answer 5

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

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

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

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

Compare this method with a similar method:

 SELECT to_json(now())::text AS has_unwanted_quotes, trim(both '"' from to_json(now())::text) AS a_bit_lengthy, to_json(now())#>>'{}' AS just_right 

This is shorter, but gives the same results.

Answer 6

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

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

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

Operator "Get [s] a JSON object at the specified path as text". However, when you specify {} as the path, it indicates the root object.

Compare this method with a similar method:

 SELECT to_json(now())::text AS has_unwanted_quotes, trim(both '"' from to_json(now())::text) AS a_bit_lengthy, to_json(now())#>>'{}' AS just_right 

This is shorter, but gives the same results.