'Preserve timezone in PostgreSQL timestamptz type

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.

postgresqltimezonetimestamppostgresql-9.3


Solution 1:[1]

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.

Solution 2:[2]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Hendy Irawan
Solution 2 gwaigh

Related Questions