77

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.

Improve this question
3
  • 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. Commented Aug 8, 2016 at 16:30
  • 1
    @BasilBourque I've simplified it. Commented Aug 8, 2016 at 16:35
  • Specify the info/tag about what software you are using to connect/query the PostgreSQL. Commented Aug 8, 2016 at 20:01

7 Answers 7

Reset to default
100

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"')
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

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
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"')
To get this to work I needed to cast now() to timestamptz rather than timestamp.
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 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.
|
55

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

Improve this answer

7 Comments

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'
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 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 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).
How do I get the fractional timezone offset? You said that it only works if the timezone is already a fraction of an hour.
|
41

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
Improve this answer

6 Comments

to_json(now()) doesn't have the T in it in Postgres 9.3
@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.
it's fine in 9.4 and 9.5, only 9.3 has the T weirdness
Got it, fixed the description, thanks again, @cdmckay
Great answer for PG9.4+
|
9

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.

Improve this answer

1 Comment

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

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)
Improve this answer

1 Comment

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
4

Simple/trivial:

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

Or if fancier needed use

REGEXP_REPLACE()
Improve this answer

1 Comment

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

I found a surprisingly easy way to deal with this problem.

I began with ugly things having various date styles in an excel spreadsheet, so I corrected some the dates manually into another copied field (date_ok).

Here's the table, at this stage, with some data:

CREATE TABLE a_few_dattes ( -- double t intended: I'm currently eating dattes
    date_original text,
    date_ok text
);

INSERT INTO a_few_dattes VALUES ('08/08/2024', '2024-08-08');
INSERT INTO a_few_dattes VALUES ('08/13/2024', '2024-08-13');
INSERT INTO a_few_dattes VALUES ('45565', '2024-09-30');
INSERT INTO a_few_dattes VALUES ('09/03/2024', '2024-09-03');
INSERT INTO a_few_dattes VALUES ('08/29/2024', '2024-08-29');
INSERT INTO a_few_dattes VALUES ('07/09/2024', '07/09/2024');
INSERT INTO a_few_dattes VALUES ('09/14/2024', '2024-09-14');
INSERT INTO a_few_dattes VALUES ('10/05/2024', '2024-10-05');
INSERT INTO a_few_dattes VALUES ('10/08/2024', '2024-10-08');
INSERT INTO a_few_dattes VALUES ('10/12/2024', '2024-10-12');
INSERT INTO a_few_dattes VALUES ('07/11/2024', '07/11/2024');
INSERT INTO a_few_dattes VALUES ('10/19/2024', '2024-10-19');

Here's the table:

SELECT * FROM a_few_dattes;
┌───────────────┬────────────┐
│ date_original │  date_ok   │
├───────────────┼────────────┤
│ 08/08/2024    │ 2024-08-08 │
│ 08/13/2024    │ 2024-08-13 │
│ 45565         │ 2024-09-30 │
│ 09/03/2024    │ 2024-09-03 │
│ 08/29/2024    │ 2024-08-29 │
│ 07/09/2024    │ 07/09/2024 │
│ 09/14/2024    │ 2024-09-14 │
│ 10/05/2024    │ 2024-10-05 │
│ 10/08/2024    │ 2024-10-08 │
│ 10/12/2024    │ 2024-10-12 │
│ 07/11/2024    │ 07/11/2024 │
│ 10/19/2024    │ 2024-10-19 │
└───────────────┴────────────┘

At this point, I ended up with various date formats. I'd just like to have some uniform formats.

Then I simply made another field with a date type, and updated it from the date_ok field with just a simple type cast:

ALTER TABLE a_few_dattes ADD COLUMN date_iso date;
UPDATE a_few_dattes SET date_iso = date_ok::date;

And here's the result:

SELECT * FROM a_few_dattes;
┌───────────────┬────────────┬────────────┐
│ date_original │  date_ok   │  date_iso  │
├───────────────┼────────────┼────────────┤
│ 08/08/2024    │ 2024-08-08 │ 2024-08-08 │
│ 08/13/2024    │ 2024-08-13 │ 2024-08-13 │
│ 45565         │ 2024-09-30 │ 2024-09-30 │
│ 09/03/2024    │ 2024-09-03 │ 2024-09-03 │
│ 08/29/2024    │ 2024-08-29 │ 2024-08-29 │
│ 07/09/2024    │ 07/09/2024 │ 2024-09-07 │
│ 09/14/2024    │ 2024-09-14 │ 2024-09-14 │
│ 10/05/2024    │ 2024-10-05 │ 2024-10-05 │
│ 10/08/2024    │ 2024-10-08 │ 2024-10-08 │
│ 10/12/2024    │ 2024-10-12 │ 2024-10-12 │
│ 07/11/2024    │ 07/11/2024 │ 2024-11-07 │
│ 10/19/2024    │ 2024-10-19 │ 2024-10-19 │
└───────────────┴────────────┴────────────┘

Time to get rid of the ugly stuff:

ALTER TABLE a_few_dattes DROP COLUMN date_original;
ALTER TABLE a_few_dattes DROP COLUMN date_ok;

And here's the final, clean, result:

SELECT * FROM a_few_dattes;
┌────────────┐
│  date_iso  │
├────────────┤
│ 2024-08-08 │
│ 2024-08-13 │
│ 2024-09-30 │
│ 2024-09-03 │
│ 2024-08-29 │
│ 2024-09-07 │
│ 2024-09-14 │
│ 2024-10-05 │
│ 2024-10-08 │
│ 2024-10-12 │
│ 2024-11-07 │
│ 2024-10-19 │
└────────────┘

I think that what happened behind the scene was that PostgreSQL interpreted the dates as valid dates, even though they had different formats, and then converted the seamlessly into ISO-8601.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.