[hibernate-dev] Supporting timezone in Timestamp Type
Steve Ebersole
steve at hibernate.org
Thu Sep 1 07:13:15 EDT 2016
Hit send accidentally...
The "problem" is normalization. You are storing a TZ that is specific to
the place (Austin, London) a shipment event occurs on the shipment event.
So even if your app had the requirment to display the shipment event in the
local TZ that that event occurred in, data-normalization says you'd store
the TZ/offset on the place and use that to render the event.
But even then, I'd personally never do that. I think rendering the
shipping event Instant in the TZ of place it happens is confusing and not
the norm anyway. I think the norm, for apps that want to render this, is
more a TZ relative to the the user. When I go to Amazon, or FedEx, etc to
track a package I could care less about the London TZ. The whole idea of a
unified "period" would mean that I see them in my local TZ (if not UTC).
Also... this will *not* involve 2 columns. We covered why in the last
email discussion we had about this a few months ago. Now your app could do
all this... map the @Temporal and then map the TZ and apply them in
memory.
On Thu, Sep 1, 2016 at 5:52 AM Steve Ebersole <steve at hibernate.org> wrote:
> 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