[hibernate-dev] Supporting timezone in Timestamp Type

Steve Ebersole steve at hibernate.org
Thu Sep 1 06:52:08 EDT 2016 

I think your example is actually a perfect example of when to not store the
TZ.

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

> > Saving in "timezoned" instants is prone to several bugs: you're then
> > exposed to ambiguities as time doesn't flow linearly in all zones.
>
> I see it the other way around: the indirection you suggest is prone to
> errors.
>
> Rules about TZ constantly change (e.g. when switching to/from DST),
> locations change time zones more often than one would expect. Saving the
> then valid TZ "next to" an UTC timestamp is much easier and more reliable
> then trying to reconstruct that info from some historical TZ DB.
>
>
> 2016-09-01 11:59 GMT+02:00 Sanne Grinovero <sanne at hibernate.org>:
>
> > To be fair I would have mapped that differently.
> > Your Shipment arrives at a certain *point in time* (long, UTC based)
> > in a certain Port (mapped with Foreign Key).
> > A Port would have a reference to a local Timezone, and UI could decide
> > to use that (or a different one) for rendering, based on other
> > aspects, like the location of the user actually looking at the UI, or
> > even a switchable user option.
> >
> > Saving in "timezoned" instants is prone to several bugs: you're then
> > exposed to ambiguities as time doesn't flow linearly in all zones.
> >
> > Storing a timezoned instant only makes sense when you're quoting a
> > political/legal source; i.e. you'ave passed under OCR some legal
> > paperwork and you need to store the date printed on the header in a
> > dedicated field, in Java8 time type you'd never use an Instant for
> > this though, there are dedicated types which make the limitations of
> > this representation clear. AFAIR some kinds of comparisons are made
> > impossible.
> >
> > I'd not spend time with improvements on Calendar: the "old Date types"
> > should be deprecated.
> >
> >
> > On 1 September 2016 at 07:46, 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
> > >>
> > > _______________________________________________
> > > 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