[hibernate-dev] Supporting timezone in Timestamp Type

Vlad Mihalcea mihalcea.vlad at gmail.com
Thu Sep 1 03:13:07 EDT 2016


For the moment, by adding a SF/Session-level configuration, we can at least
save Timestamps in UTC, which is indeed a very common scenario.

Later on, we can address the situation that you described in this email by
adding new types or introducing a new annotation to specify the secondary
field where the TZ info should be stored.


On Thu, Sep 1, 2016 at 9:46 AM, Gunnar Morling <gunnar at hibernate.org> wrote:

> > it is a huge mistake to save the specific TZ differences to the DB
> I once was working on a globally deployed shipping management application
> where that was a requirement.
> Say there is a shipment from Austin (one TZ) to London (another TZ). In
> that application an operator based in Hamburg (yet another TZ) should be
> able to know when the shipment was brought in in Austin (the local time in
> Austin at that point) and when it was delivered in Hamburg (again the
> local time there at that point).
> So we stored all dates in UTC, but also the TZ (in a *different column*; I
> don't quite remember whether it was just a string with the TZ (offset) or
> again the entire date-time using TIMESTAMP_WITH_TZ). Storing it in UTC
> allowed for comparisons whereas the other column allowed to show each date
> in its local TZ. So an operator in Hamburg (at CET) could see a delivery
> was brought in in Austin at 8:32 CST.
> Something like that would still be my preference for handling TZ-bound
> types such as ZonedDateTime: store the UTC timestamp in one column and the
> TZ info (either id or offset from UTC) in another.
> Relying on the JVM TZ or something like a single "application TZ" won't
> cut it for cases like the one above: the operator in Hamburg wants to see
> dates from Austin and London, using their respective time zones. Instead,
> the TZ of the incoming ZonedDateTime would have to be persisted so it's
> available when reading it back.
> 2016-08-31 20:51 GMT+02:00 Steve Ebersole <steve at hibernate.org>:
>> We discussed this on HipChat, but for the benefit of all on this
>> discussion...
>> Part of this (the original report) speaks to a difference in how we map
>> (org.hibernate.type.Type) java.time temporal versus a java.util temporal -
>> specifically java.util.Calendar.  When we are passed a Calendar (the model
>> defines its attribute as Calendar) we pass along that Calendar reference
>> to
>> the JDBC driver which forces the driver to use that Calendar's TZ.  For
>> java.time temporal types, we always resolve them to UTC values mainly
>> because of how we get to the java.sql.Timestamp value from the java.time
>> value.  For example, for ZonedDateTime we call:
>>     return (X) Timestamp.from( zonedDateTime.toInstant() );
>> The call to #toInstant there essentially forces the value to UTC because
>> it
>> converted directly to epoch-based numeric.
>> Thinking about this some more and "stepping back", the real problem is
>> that
>> there are a few different timezones in play here that need to be balanced
>> and handled:
>>    1. The database default TZ
>>    2. The stored TZ
>>    3. The JVM/JDBC TZ
>>    4. The application TZ
>> The whole idea of "stored TZ" really depends on how the database/driver
>> treats TIMESTAMP and whether it supports a "TIMESTAMP_WITH_TIMEZONE"
>> type.  I
>> personally think it is a huge mistake to save the specific TZ differences
>> to the DB, so I would personally continue to not support
>> types.  This would mean we never have to bind the Calendar because we
>> could
>> simply convert the value to to the JVM/JDBC TZ ourselves.  Specifically I
>> would suggest that we (continue to) assume that the driver has been set up
>> such that the same TZ is used when
>>    1. when binding a Timestamp (without passing a Calendar)
>>    2. reading a Timestamp (without passing a Calendar).
>> Specifically this would mean setting the JVM TZ, or for JDBC drivers that
>> support it setting the "default TZ" on the driver.  If we start storing
>> values in the TZ specific to the Calendar we really have no idea how to
>> properly read those values back because we'd lose context to that TZ.  For
>> that reason I suggest we drop passing along the Calender and instead
>> perform all these conversions in memory and pass the converted Timestamp
>> to
>> JDBC.  When reading back, that should mean the driver would return us
>> Timestamps relative to that same TZ.
>> The last piece is "application TZ" which would be an optional setting
>> indicating the TZ zone-based values should be returned in.
>> E.g., let's say a user maps a ZonedDataTime using @Temporal(TIMESTAMP).
>> If
>> following my suggestions, when saving these values we would:
>>    1. convert the ZonedDateTime to a ZonedDateTime in the "JVM/JDBC TZ"
>> (if
>>    different from the incoming ZonedDateTime's TZ)
>>    2. use that to construct a Timestamp to bind to JDBC
>> When reading back these values, we would:
>>    1. get back the Timestamp from JDBC
>>    2. assume the value is relative to the "JVM/JDBC TZ" and build the
>>    ZonedDateTime via `ZonedDateTime.ofInstant( timestamp.toInstant(),
>>    jvmJdbcZoneId )`
>>    3. convert that built ZonedDateTime to the application TZ:
>>    `builtZonedDateTime.withZoneSameInstant( applicationZoneId )` - it
>> might
>>    be possible to combine this step with the previous, not sure...
>> Some if this is type-specific.  Assuming the suggestions above:
>>    - Instant - inherently epoch-based.
>>       - to convert this to a Timestamp for binding we would use
>> `Timestamp#from(Instant).
>>       Because of the above assumptions, the driver would interpret
>> this relative
>>       to JVM/JDBC TZ
>>       - Reading back we simply use `Timestamp#toInstant()`
>>    - LocalDateTime - is the relative idea of a date+time outside the
>>    context of any specific TZ.
>>       - When binding we would resolve this into the JVM/JDBC TZ (using
>>       LocalDateTime#atZone(jvmJdbcZoneId), convert to an Instant and
>> create a
>>       Timestamp and bind that Timestamp to JDBC.
>>       - When reading back we'd get the Instant from Timestamp and use that
>>       in `LocalDateTime#ofInstant` using the application TZ
>>    - LocalDate - relative idea of a date outside of any specific TZ.  Of
>>    course this could also be mapped to a JDBC DATE which circumvents some
>> of
>>    this, but mapped to a TIMESTAMP...
>>       - When binding we'd convert to a LocalDateTime using
>>       `LocalDate#atStartOfDay` and treat that LocalDateTime as above.
>>       - When reading back we would create a LocalDateTime using
>> `LocalDateTime.ofInstant(timestamp.toInstant(),
>>       applicationZoneId )`
>>    - LocalTime - relative idea of a time outside any specific TZ.  Could
>>    also be mapped to a JDBC TIME...
>>       - When binding we'd create a LocalDateTime using `LocalTime.atDate(
>>       LocalDate.of( 1970, 1, 1 ) )`, converting that to a ZonedDateTime
>> using
>>       `LocalDateTime#atZone( jvmJdbcZoneId )` and finally converting that
>> to a
>>       Timestamp
>>       - When reading we would call `LocalDateTime.ofInstant(
>>       timestamp.toInstant(), applicationZoneId ).toLocalTime()`
>>    - ZonedDateTime - like a Calendar bundles a date+time with a TZ, and so
>>    is directly convertible to an epoch-based value (Instant, Timestamp,
>> etc).
>>       - When binding we would convert this to an Instant and then to a
>>       Timestamp
>>       - When reading we would convert the Timestamp to an Instant and then
>>       convert the Instant to a ZonedDateTime using
>> `ZonedDateTime.ofInstant(
>>       timestamp.toInstant(), applicationZoneId )`
>>    - OffsetDateTime - handled similar to ZonedDateTime.
>>       - When binding we would convert the value to a ZonedDateTime using
>>       `OffsetDateTime#atZoneSameInstant( jvmJdbcZoneId )` and then
>> follows
>>       ZonedDateTime
>>       - When reading we would use `OffsetDateTime.ofInstant(
>>       timestamp.toInstant(), applicationZoneId )`
>>    - OffsetTime - atm we just drop the ZoneOffset
>>    - for java.util.Date and java.sql.Timstamp mapping we have to decide
>>    what those concepts model in regards to their value relative to a TZ.
>> Are
>>    they a LocalDateTime relative to the system TimeZone?  Are they a
>>    LocalDateTime relative to UTC?  At the moment how we handled these
>> "legacy
>>    Java temporal types" ends up answering this question as them being "a
>>    LocalDateTime relative to the system TimeZone".
>>    - what about java.sql.Time?  Is this a LocalTime?  An OffsetTime
>>    relative to UTC (+0)?
>>    - and java.sql.Date?
>> Lot of moving parts when you actually start looking deep at "supporting
>> timezones".
>> [1]
>> http://stackoverflow.com/questions/18447995/postgresql-9-2-
>> jdbc-driver-uses-client-time-zone,
>> etc
>> On Wed, Aug 31, 2016 at 9:10 AM Vlad Mihalcea <mihalcea.vlad at gmail.com>
>> wrote:
>> > Hi,
>> >
>> > While reviewing the Pull Request for this issue:
>> > https://github.com/hibernate/hibernate-orm/pull/1536
>> >
>> > I realized that we can improve the default TimestampType as follows:
>> >
>> > 1. We could make it parameterizable so that it can also take a custom
>> > timezone (UTC) during mapping.
>> > 2. We could also define a default timezone so that we don't rely on the
>> JVM
>> > one. This might be desirable when the UI requires to display the time
>> in a
>> > certain timezone, while we want to save all timestamps in UTC.
>> >
>> > Let me know what you think.
>> >
>> > Vlad
>> > _______________________________________________
>> > hibernate-dev mailing list
>> > hibernate-dev at lists.jboss.org
>> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>> >
>> _______________________________________________
>> hibernate-dev mailing list
>> hibernate-dev at lists.jboss.org
>> https://lists.jboss.org/mailman/listinfo/hibernate-dev

More information about the hibernate-dev mailing list