[hibernate-dev] Supporting timezone in Timestamp Type

Gunnar Morling gunnar at hibernate.org
Thu Sep 1 03:19:33 EDT 2016


Regarding

> LocalDateTime [...]
> When binding we would resolve this into the JVM/JDBC TZ (using
> LocalDateTime#atZone(jvmJdbcZoneId)

Couldn't you rather use UTC when handling these TZ-less types?

This will make the solution more robust in cases where you requirement "JVM
TZ when saving = JVM TZ when reading back" isn't met. As these types have
no TZ component, the JVM TZ should take no influence whatsoever.


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