CODEWITHSUNDEEP
sqlpostgresqldatetimepostgresql-9.3
Simon Warta
Simon Warta

Reputation: 11418

Convert rfc3339 string to timestamp with timezone

Is there any way to properly convert a rfc3339 string in a character varying column to a timestamp with timezone?

Input: "2015-01-28T17:41:52Z"
Expected output: "2015-01-28 17:41:52+00"
Current output: "2015-01-28 17:41:52+01"

I tried the following

/* begin dummy data */
WITH messages as (
    select 123 as id, '2015-01-28T17:41:52Z'::text as received
)
/* end dummy data */
SELECT id, received, (to_timestamp(received, 'YYYY-MM-DDThh24:MI:SS')) as d
FROM messages

Adding TZ or tz is not possible for input data and OF is available in 9.4 but not in 9.3.

Upvotes: 4

Views: 5551

Answers (1)

pozs
pozs

Reputation: 36274

RFC3339 is just a profile of ISO 8601.

PostregreSQL accepts any valid ISO 8601 input as date/timestamp/timestamp with time zone. Try casting, like:

SELECT '2015-01-28T17:41:52Z'::timestamptz

Note: however, your output (in your client) will always be in your current time zone.

Upvotes: 7

Related Questions