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 <gun...@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 <sa...@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 <gun...@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 <st...@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.v...@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@lists.jboss.org > > >> > https://lists.jboss.org/mailman/listinfo/hibernate-dev > > >> > > > >> _______________________________________________ > > >> hibernate-dev mailing list > > >> hibernate-dev@lists.jboss.org > > >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > > >> > > > _______________________________________________ > > > hibernate-dev mailing list > > > hibernate-dev@lists.jboss.org > > > https://lists.jboss.org/mailman/listinfo/hibernate-dev > > > _______________________________________________ > hibernate-dev mailing list > hibernate-dev@lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev > _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
