Postgres: JSONB string to timestamp with TZ

In postgres database (9.4) i have data saved as JSON object. Including some datetime elements. In otder to to 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 ?


Source: json

Leave a Reply