Menu
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.
369
Use dd/mm/yyyy for numeric date representations. Use mm/dd/yyyy for numeric date representations. A value for SET DATESTYLE can be one from the first list (output styles), or one from the second list (substyles), or one from each separated by a comma.
Postgres DATE data type Postgres uses the DATE data type for storing different dates in YYYY-MM-DD format. It uses 4 bytes for storing a date value in a column. You can design a Postgres table with a DATE column and use the keyword DEFAULT CURRENT_DATE to use the current system date as the default value in this column.
PostgreSQL stores the timestamptz in UTC value. When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table.
In this PostgreSQL tutorial, we will learn about PostgreSQL DATE Format with a few examples. We use the DATE data type in PostgreSQL to store DATE type values in a table.
RFC is a formal document from the Internet Engineering Task Force (IETF) that is the result of committee drafting and subsequent review by interested parties¹ There are already so many RFC documents released by this committee. And become standard in every business. One of their documents is the RFC 3339, a document for DateTime formatting.
Now we will use the TO_CHAR () function in SELECT statement to get the TIMESTAMP in DD/MM/YYYY HH12:MI format: We have specified the DD/MM/YYYY HH24:MI format in the TO_CHAR () function inside the PostgreSQL SELECT statement to display the TIMESTAMP in 24 HOUR Format.
string: It is a string that we will convert into a date. format: It is a pattern or format of a date. Let’s understand through different examples with different formats. This is an example of PostgreSQL date format convert.
Closest I've come to it is this:
postgres=# select to_char(('1970-01-01T00:00:00Z'::timestamp), 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"');
to_char
-----------------------------
1970-01-01T00:00:00.000000Z
(1 row)
This provides the T and the microseconds, but not the time zone.
86
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
