Hi, For the moment, by adding a SF/Session-level configuration, we can at least save Timestamps in UTC, which is indeed a very common scenario.
Later on, we can address the situation that you described in this email by adding new types or introducing a new annotation to specify the secondary field where the TZ info should be stored. Vlad On Thu, Sep 1, 2016 at 9:46 AM, 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
