[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