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 <st...@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 <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
