16

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.

markdsievers
  • 7,151
  • 11
  • 51
  • 83
  • Is it possible to store the offset in a separate column so you're not at the mercy of Postgres? – tadman Nov 28 '13 at 21:26
  • 2
    @tadman Ha. Trying to roll this into one column. Doesn't seem to be like an unreasonable thing to do. – markdsievers Nov 28 '13 at 21:29
  • What is the data source? A string literal? Or another column - of what type? – Erwin Brandstetter Nov 28 '13 at 22:11
  • @ErwinBrandstetter A validated ISO8601 string. – markdsievers Nov 28 '13 at 22:20
  • 1
    Your format isn't compliant with ISO 8601. A compliant format would look like this: 2004-10-19T10:23:54+02:00. (Notes about that are in your linked doc.) PostgreSQL does indeed store timestamps in UTC. For timezone aware columns, it converts *from* the client's timezone on input and converts *to* a (possibly) different client's timezone on output. Application code doesn't need to do any conversion, but might need to set its time zone. (For a session, `set time zone 'America/Anchorage';`. – Mike Sherrill 'Cat Recall' Nov 28 '13 at 22:26
  • @MikeSherrill'Catcall' Incorrect. See this [article for timezones](http://en.wikipedia.org/wiki/ISO_8601#Time_offsets_from_UTC) – markdsievers Nov 28 '13 at 22:33
  • @markdsievers: Ha! I didn't know you could abbreviate the time zone offset. Does the current ISO 8601 allow spaces? Older versions did not--they required "T" or nothing to mark the start of the time part. (ISO 8601 used to say something like "The space character shall not be used.") – Mike Sherrill 'Cat Recall' Nov 29 '13 at 02:38
  • @MikeSherrill'Catcall' At least that article mentions `It is permitted to omit the 'T' character by mutual agreement`. Internally I'm using the 'T', though my example in the question was pulled straight from the postgres docs. Postgres is pretty flexible on its date formats, so both versions of the delimiter are acceptable values. – markdsievers Nov 29 '13 at 03:04
  • @MikeSherrill'Catcall' Your correction for a compliant ISO 8601 format is not quite correct. You were correct in that the `T` in the middle is (virtually/practically) required (optional if both sending & receiving parties agree). However, the `+02` is correct in itself and does *not* need to be changed to `+02:00`. The **minutes portion of the offset is optional**. As the [Wikipedia page says](https://en.wikipedia.org/wiki/ISO_8601#Time_offsets_from_UTC): `The offset from UTC is given in the format ±[hh]:[mm], ±[hh][mm], or ±[hh].` – Basil Bourque Feb 15 '14 at 09:15

3 Answers3

25

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:

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 4
    Thanks for the detailed response. So disappointing this is not possible in one column. – markdsievers Nov 28 '13 at 23:49
  • 4
    @markdsievers If you think about it, the offset is superfluous information. The "real" time is UTC/GMT, the number of milliseconds since the epoch. If in the context of your app and data you truly cared about preserving the offset, that means you care about the local time, and that means you should be capturing and recording the time zone. Ex: "Pacific/Auckland". The time zone is more than an offset, it includes rules/history for Daylight Savings Time (DST) and other anomalies. Without a time zone being recorded, there is no useful distinction between a date-time+offset and a date-time in UTC. – Basil Bourque Feb 15 '14 at 09:26
  • 6 year older me sees the error of his ways. Tisk tisk, young self. Thanks Erwin and Basil. – markdsievers Oct 02 '19 at 23:53
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.

Hendy Irawan
  • 20,498
  • 11
  • 103
  • 114
1

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.

gwaigh
  • 1,182
  • 1
  • 11
  • 23
  • Deliberately left out the ORM details as that is the subject of another subsequent question. This question simply addresses the possibility of storing a TZ in one column of a Postgres timestamptz type, as the title suggests. – markdsievers Nov 28 '13 at 23:53