Preserve timezone in PostgreSQL timestamptz type

Question

For an ISO8601 compliant datetime

2004-10-19 10:23:54+02

Is it possible to have that value, with +02 offset, reflected in the stored column value and also preserved when it is selected?

From my reading of the appropriate section of the docs Postgres' default behavior is to convert to UTC at which point the original offset is lost. This is certainly what I'm seeing.

The data is accessed via an ORM that is not able to add any special tz conversion so I really need to simply store the datetime with original offset and have the value reflected when selected.

For anyone dying to tell me it's the same instance in time, the preservation of this value has significance to this data.

Answer 1

As you already figured out yourself, the time zone is not saved at all with Postgres date / time types, not even with timestamptz. Its role is just an input modifier or output decorator, respectively. Only the value (the point in time) is saved. Ample details in this related answer:

• Ignoring time zones for Postgres timestamps

Therefore, if you want to preserve that part of the input string, you have to extract it from the string and save it yourself. I would use a table like:

CREATE TABLE tstz
 ...
 , ts timestamp    -- without time zone
 , tz text
)

tz, being text, can hold a numeric offset as well as a time zone abbreviation, or a time zone name.

The difficulty is to extract the time zone part according to all the various rules the parser follows and in a way that won't break easily. Instead of cooking up your own procedure, make the parser do the work. Consider this demo:

WITH ts_literals (tstz) AS (
   VALUES ('2013-11-28 23:09:11.761166+03'::text)
        , ('2013-11-28 23:09:11.761166 CET')
        , ('2013-11-28 23:09:11.761166 America/New_York')
   )
SELECT tstz
     , tstz::timestamp AS ts
     , right(tstz, -1 * length(tstz::timestamp::text)) AS tz
FROM   ts_literals;

fiddle
_{Old sqlfiddle}

Works with or without a T between date and time. The key logic is here:

right(tstz, -1 * length(tstz::timestamp::text)) AS tz

Take what's left of a timestamp string after trimming the length of what the parser identified as date / time component. This relies on the input being, as you stated:

validated ISO 8601 strings