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 altogether in Rails and PostgreSQL

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;

db<>fiddle here
_{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

Answer 2

Java developers can use Joda Time combined with Jadira UserType's PersistentDateTimeAndZone. Example:

@Basic(optional = false)
@Columns(columns = { @Column(name = "modificationtime"),
        @Column(name = "modificationtime_zone") })
@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTimeAndZone")
@Index(name = "payment_modificationtime_idx")
private DateTime modificationTime = null;

In this example, the DateTime information is in 2 columns:

1. modificationtime timestamp without time zone to store the timestamp in UTC time zone
2. modificationtime_zone varchar(255) to store the time zone ID as string (e.g. America/Caracas)

While Joda Time and Jadira (and Hibernate) is specific to Java (and is the de facto approach), the above approach of structuring the RDBMS columns to store both timestamp and time zone can be applied to any programming language.

Answer 3

The native postgres date/time datatypes are not going to preserve your input timezone for you. If you need to both query it as a timestamp in the database and present the original information, you are going to have to store both pieces of information in some fashion.

I was going to suggest your ORM could define custom inflate/deflate methods to handle the magic, but apparently it cannot. You should indicate which ORM you are using.

You could have the ORM store/retrieve the string in the database and use a trigger in Postgres to convert that to a timestamptz stored in another column that is used when doing database side queries. If you have many tables with this type of data, that could be a bit unwieldy.

If you really want the data in a single column in the database, you could define a composite type in Postgres, though your ORM may not be able to deal with them.