Kpihus Kpihus - 2 months ago 5
JSON Question

Postgres: JSONB string to timestamp with TZ

In postgres database (9.4) i have data saved as JSON object. Including some datetime elements. In order to do time comparision in sql query I have to convert this string to timestamp. I have tried this query:

SELECT to_timestamp('2015-05-22T10:56:04.949Z','YYYY-MM-DD HH:MI:SS.MS');
-- dummy query just to test converting

OUTPUT: 2015-05-22 10:56:04.949+03

It works basically fine, except, I cant figure out how to include timezone parameter there. This string is in UTC time 10:56AM, if I run this query, I'll get 10:56 in +03 timezone (+03 is my local timezone). According to Postgres documentation, TZ should be added to format string to get timezone as well, but it does not work.

How to format this string so that it includes also timezone from initial date-time string ?


As far as I know it's not possible to parse timezone from string.
However if you know your timestamp is always in UTC timezone you can use:

SELECT to_timestamp('2015-05-22T10:56:04.949Z','YYYY-MM-DD HH:MI:SS.MS')::timestamp at time zone '00:00'