Обсуждение: Postgres RFC3339 datetime formatting
Is there any way to format datetime to RFC3339Nano
Eg: 2006-01-02T15:04:05.999999999Z07:00
in postgres 9.3 or 9.5?
I tried with to_char
. But there is no documentation how to handle T
, Z
, +07:00
, -07:00
etc.
The nearest one I can reach is
v2=# select to_char(current_timestamp, 'YYYY-MM-DD HH:MI:SS.MSOF'); to_char
----------------------------2016-05-08 12:16:14.493+04
Which is default JSON output format datetime in postgres 9.3. Please see below.
psql (9.5.1, server 9.3.6)
Type "help" for help.
fetchrdb=> select to_json(current_timestamp); to_json
---------------------------------"2016-05-08 11:58:04.844548+04"
(1 row)
In the case of JSON encoded output from postgres 9.5 is RFC3339Nano
eg:
psql (9.5.1)
Type "help" for help.
v2=# select to_json(current_timestamp); to_json
------------------------------------"2016-05-08T11:59:17.121411+04:00"
I could't find an option to format datetime to RFC3339Nano in postgres 9.3 or 9.5 using to_char
.
http://www.postgresql.org/docs/9.5/static/functions-formatting.html
Is there any hidden option/functions you use to achieve the same? Any help regarding is appreciated.
Jasim Mohd wrote: > Is there any way to format datetime to RFC3339Nano Eg: 2006-01-02T15:04:05.999999999Z07:00 in postgres > 9.3 or 9.5? > > I tried with to_char. But there is no documentation how to handle T, Z, +07:00, -07:00 etc. The best I can get is: SELECT to_char(current_timestamp, 'FXYYYY-MM-DD"T"HH:MI:SS.US"000Z"OF'); but it will suppress the minute part of the time zone offset if it is 0. I don't think you can get nanosecond precision in PostgreSQL. Is that good enough? Yours, Laurenz Albe
Is there any way to format datetime to
RFC3339Nano
Eg:2006-01-02T15:04:05.999999999Z07:00
in postgres 9.3 or 9.5?I tried with
to_char
. But there is no documentation how to handleT
,Z
,+07:00
,-07:00
etc.The nearest one I can reach is
v2=# select to_char(current_timestamp, 'YYYY-MM-DD HH:MI:SS.MSOF'); to_char ----------------------------2016-05-08 12:16:14.493+04
Which is default JSON output format datetime in postgres 9.3. Please see below.
psql (9.5.1, server 9.3.6) Type "help" for help. fetchrdb=> select to_json(current_timestamp); to_json ---------------------------------"2016-05-08 11:58:04.844548+04" (1 row)
In the case of JSON encoded output from postgres 9.5 is
RFC3339Nano
eg:psql (9.5.1) Type "help" for help. v2=# select to_json(current_timestamp); to_json ------------------------------------"2016-05-08T11:59:17.121411+04:00"
I could't find an option to format datetime to RFC3339Nano in postgres 9.3 or 9.5 using
to_char
.http://www.postgresql.org/docs/9.5/static/functions-formatting.html
Is there any hidden option/functions you use to achieve the same? Any help regarding is appreciated.
--
John McKown