Search code examples
postgresqltimestamp-with-timezone

How do I convert a string of the form 'YYYY-DD-MM HH:MI:SS.MS +00:00' where last four chars are a timezone offset into a postgres timestamptz?

How do I convert a string of the form 'YYYY-DD-MM HH:MI:SS.MS +00:00' where last four chars are a timezone offset into a postgres timestamptz?

This does not seem to work:

select '2018-06-13 04:11:46.873 -07:00'::timestamptz;
        timestamptz         
----------------------------
 2018-06-13 11:11:46.873+00
(1 row)
Solution

  • The output is correct. The type timestamp with time zone is a bit misleading and the behaviour is different than e.g. in Oracle.

    quote from the manual

    For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone.

    The input '2018-06-13 04:11:46.873 -07:00'::timestamptz is converted to the corresponding UTC value based on the time zone offset given. That value is then convert to your session time zone and sent to the client which then results in 2018-06-13 11:11:46.873+00 in your session's time zone.