Re: [GENERAL] Postgres RFC3339 datetime formatting

[John McKown]

Cheat?

# select translate(to_char(current_timestamp, '-MM-DD HH:MI:SS.MSOF'),'
','T');
 translate

 2016-05-10T11:42:20.479-05
(1 row)

Just translate the blank in the result of to_char() to a T.

On Sun, May 8, 2016 at 3:59 AM, Jasim Mohd  wrote:

> Is there any way to format datetime to RFC3339Nano Eg:
> 2006-01-02T15:04:05.9Z07: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, '-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.
>
> --
> Thanks & Regards
> skype: jasim.mk
>



-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

Re: [GENERAL] Postgres RFC3339 datetime formatting

[Albe Laurenz]

Jasim Mohd wrote:
> Is there any way to format datetime to RFC3339Nano Eg: 
> 2006-01-02T15:04:05.9Z07: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, 'FX-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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgres RFC3339 datetime formatting

[Jasim Mohd]

Is there any way to format datetime to RFC3339Nano Eg:
2006-01-02T15:04:05.9Z07: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, '-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.

-- 
Thanks & Regards
skype: jasim.mk