Save timezone in type timestamptz PostgreSQL

Question

In accordance with ISO8601 datetime

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

Is it possible to have this value with an offset of +02 reflected in the saved value of the column, as well as saved when it is selected?

From my reading of the relevant section of the documents . The default behavior of Postgres is to convert to UTC, in which the original offset is lost. This is definitely what I see.

Data is accessed via ORM, which cannot add any special tz transformation, so I really just need to save the date-time with the original offset and display the value when it is selected.

For those who die, to tell me the exact same instance in time, saving this value matters to this data.

Answer 1

As you already found out, the time zone is not saved at all with Postgres date / time types, even with timestamptz . Its role is only an input modifier or output decoder, respectively. Only the value (point in time) is saved. Enough details in this answer:

• Ignoring Time Zones in General in Rails and PostgreSQL

Therefore, if you want to save this part of the input string, you need 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 , may contain a numerical offset , as well as a time zone abbreviation or time zone name .

The difficulty is to extract a part of the time zone in accordance with all the various rules that the parser follows, and in such a way that it will not break easily. Instead of preparing 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; 

SQL Fiddle

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

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

Take what remains of the timestamp string after trimming the length of what the parser defines as a date / time component. It depends on the input, as you stated:

ISO8601 verified strings

Answer 2

Java developers can use Joda Time in conjunction with the Jadira UserType 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 two columns:

• modificationtime timestamp without time zone to save timestamp in UTC timezone
• modificationtime_zone varchar(255) to save the timezone identifier as a string (e.g. America/Caracas )

Although Joda Time and Jadira (and Hibernate) are Java specific (and this is a de facto approach), the above approach to structuring RDBMS columns to store both timestamp and timezone can be applied to any programming language.

Answer 3

Native postgres date / time data types will not save your time zone for you. If you need to both request it as a timestamp in the database and present the initial information, you will have to somehow store both parts.

I was going to suggest that your ORM could define custom inflation / deflation methods to handle magic, but apparently this can't. You must indicate which ORM you are using.

You can have an ORR repository / get a row in the database and use trigger in Postgres to convert this to a stored timestamptz in another column that is used when executing queries on the database side. If you have many tables with a given data type, this can be a bit cumbersome.

If you really need data in a single column in the database, you can define a composite type in Postgres, although your ORM may not be able to handle it.