[hibernate-dev] Supporting timezone in Timestamp Type
Vlad Mihalcea
mihalcea.vlad at gmail.com
Thu Sep 1 03:13:07 EDT 2016
Hi,
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.
Vlad
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
>> TIMESTAMP_WITH_TIMEZONE
>> 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