In fact I'd even recommend to use a string-based column type to store LDT, as it avoids these kinds of issues altogether.

IMO no timezone conversion whatsoever should be applied when persisting LocalDateTime as it doesn't contain any TZ information. If the target column type requires a TZ, it should be set to UTC, storing the given value without any shift. I.e. the LDT value 2007-12-03T10:15:30 should be stored as 2007-12-03T10:15:30@UTC, no matter what's the VM's timezone or any TZ related config. This allows to retrieve the original value later on, also if e.g. the loading VM is in a different TZ. In fact I'd even recommend to use a string-based column type to store LDT, as it avoids these kinds of issues altogether. --Gunnar Am Fr., 5. Okt. 2018 um 07:15 Uhr schrieb Vlad Mihalcea <mihalcea.vlad(a)gmail.com&gt;: > > Hi, > While reviewing this Jira issue: > > https://hibernate.atlassian.net/browse/HHH-12988 > > and further discussing it via Twitter, I wonder if we should persist > LocalTime as-is without any TimeZone transformation > that may be done if we enable `hibernate.jdbc.time_zone`? > > According to the Date/Time API, LocalTime should not be relative to any > TimeZone. > > If we make this change, it means we need to use a LocalTime SQL descriptor > that ignores the jdbc.time_zone property, > and the change is going to break compatibility as well. > > Vlad > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

Hi IMO no timezone conversion whatsoever should be applied when > persisting LocalDateTime as it doesn't contain any TZ information. That's not very easy to do since either the JDBC Driver or the database engine might to the timezone conversion based on the underlying setting. In fact I'd even recommend to use a string-based column type to store > LDT, as it avoids these kinds of issues altogether.

Actually, a Long (BigInt) column type with the epoch would be more suitable than a CHAR-based column since it's more compact and achieves the same goal. However, many clients will not want to store Date/Time in Int or String columns as explained in the following article:

https://www.periscopedata.com/blog/better-sql-schema Maybe introducing another value for "hibernate.jdbc.time_zone" ("as_is"?) > that would also impact how we map timezone-less types, would be a good idea? We would have to introduce a new configuration property as a strategy where the current behavior is "legacy" while the new one must be explicitly enabled. Vlad On Fri, Oct 5, 2018 at 11:17 AM Yoann Rodiere <yoann(a)hibernate.org&gt; wrote: > > In fact I'd even recommend to use a string-based column type to store > > LDT, as it avoids these kinds of issues altogether. > > Or just, you know, "timestamp without timezone". Where possible. > > More to the point, I agree with Vlad's proposition, and I also think ORM > should avoid messing with timezones as much as possible: when the user > didn't provide one (LocalDate, LocalDateTime, Date), don't store one, when > he provided one (ZonedDateTime, OffsetDateTime, Calendar, ...), store it > exactly as provided. The goal being to return the exact value that was > persisted when later retrieving the data. > But unfortunately I think there is a lot of legacy behaviors that differ > from this, so any attempt at "fixing" it would break compatibility and make > someone angry. > > Maybe introducing another value for "hibernate.jdbc.time_zone" ("as_is"?) > that would also impact how we map timezone-less types, would be a good idea? > > > Yoann Rodière > Hibernate NoORM Team > yoann(a)hibernate.org > > > On Fri, 5 Oct 2018 at 09:38, Gunnar Morling <gunnar(a)hibernate.org&gt; wrote: > >> IMO no timezone conversion whatsoever should be applied when >> persisting LocalDateTime as it doesn't contain any TZ information. >> >> If the target column type requires a TZ, it should be set to UTC, >> storing the given value without any shift. I.e. the LDT value >> 2007-12-03T10:15:30 should be stored as 2007-12-03T10:15:30@UTC, no >> matter what's the VM's timezone or any TZ related config. This allows >> to retrieve the original value later on, also if e.g. the loading VM >> is in a different TZ. >> >> In fact I'd even recommend to use a string-based column type to store >> LDT, as it avoids these kinds of issues altogether. >> >> --Gunnar >> >> >> >> >> >> Am Fr., 5. Okt. 2018 um 07:15 Uhr schrieb Vlad Mihalcea >> <mihalcea.vlad(a)gmail.com&gt;: >> > >> > Hi, >> > While reviewing this Jira issue: >> > >> > https://hibernate.atlassian.net/browse/HHH-12988 >> > >> > and further discussing it via Twitter, I wonder if we should persist >> > LocalTime as-is without any TimeZone transformation >> > that may be done if we enable `hibernate.jdbc.time_zone`? >> > >> > According to the Date/Time API, LocalTime should not be relative to any >> > TimeZone. >> > >> > If we make this change, it means we need to use a LocalTime SQL >> descriptor >> > that ignores the jdbc.time_zone property, >> > and the change is going to break compatibility as well. >> > >> > Vlad >> > _______________________________________________ >> > hibernate-dev mailing list >> > hibernate-dev(a)lists.jboss.org >> > https://lists.jboss.org/mailman/listinfo/hibernate-dev >> _______________________________________________ >> hibernate-dev mailing list >> hibernate-dev(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> > _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

Ah good, its time for this yearly discussion :D

The problem is as Vlad points out. We are kind of forced to do *something* wrt timezone to often times counteract what the JDBC driver will do. As I have always contended, imo that saving a date into the database with any kind of timzone (other than UTC) is simply wrong. I personally would never do it - and I think we've seen enough of the down sides to doing it. As Sanne points out, storing epoch-based dates (Instant, etc) is always the best option One cool option would be an AttributeConverter that handles the timezone transformation, combined with telling Hibernate to always use UTC for the JDBC timezone On Fri, Oct 5, 2018 at 4:44 AM Sanne Grinovero <sanne(a)hibernate.org&gt; wrote: > > On Fri, 5 Oct 2018 at 10:28, Vlad Mihalcea <mihalcea.vlad(a)gmail.com&gt; wrote: > > > > Hi > > > > IMO no timezone conversion whatsoever should be applied when > > > persisting LocalDateTime as it doesn't contain any TZ information. > > > > > > That's not very easy to do since either the JDBC Driver or the database > > engine might to the timezone conversion based on the underlying setting. > > > > In fact I'd even recommend to use a string-based column type to store > > > LDT, as it avoids these kinds of issues altogether. > > +1 I think we need to enforce that. Mapping a Java type which is > explicitly designed to be insensitive to TZ should not be stored into > a type which is sensitive to it. That would otherwise defeat the > purpose of using these specific types. > > If people really want to convert stuff, that's business logic. So that > belong in the business layer: let them do explicit conversion in their > own code before invoking the setter of an entity, that will also help > to bring awareness on any conversion issue they might have. > > > > > > > Actually, a Long (BigInt) column type with the epoch would be more suitable > > than a CHAR-based column since it's more compact and achieves the same goal. > > However, many clients will not want to store Date/Time in Int or String > > columns as explained in the following article: > > -1 > > let's use Epoch based numerics for Java types which are based on > Epoch. e.g. java.time.Instant. > > People can always transform a date into an epoch-independent number by > simply encoding it as a number; > e.g. "2018-10-31" -> 20181031 . I guess it could save you some bytes > but I'd be skeptical on doing this automatically. > > > > > https://www.periscopedata.com/blog/better-sql-schema > > > > Maybe introducing another value for "hibernate.jdbc.time_zone" ("as_is"?) > > > that would also impact how we map timezone-less types, would be a good idea? > > > > > > We would have to introduce a new configuration property as a strategy where > > the current behavior is "legacy" > > while the new one must be explicitly enabled. > > > > Vlad > > > > On Fri, Oct 5, 2018 at 11:17 AM Yoann Rodiere <yoann(a)hibernate.org&gt; wrote: > > > > > > In fact I'd even recommend to use a string-based column type to store > > > > LDT, as it avoids these kinds of issues altogether. > > > > > > Or just, you know, "timestamp without timezone". Where possible. > > > > > > More to the point, I agree with Vlad's proposition, and I also think ORM > > > should avoid messing with timezones as much as possible: when the user > > > didn't provide one (LocalDate, LocalDateTime, Date), don't store one, when > > > he provided one (ZonedDateTime, OffsetDateTime, Calendar, ...), store it > > > exactly as provided. The goal being to return the exact value that was > > > persisted when later retrieving the data. > > > But unfortunately I think there is a lot of legacy behaviors that differ > > > from this, so any attempt at "fixing" it would break compatibility and make > > > someone angry. > > > > > > Maybe introducing another value for "hibernate.jdbc.time_zone" ("as_is"?) > > > that would also impact how we map timezone-less types, would be a good idea? > > > > > > > > > Yoann Rodière > > > Hibernate NoORM Team > > > yoann(a)hibernate.org > > > > > > > > > On Fri, 5 Oct 2018 at 09:38, Gunnar Morling <gunnar(a)hibernate.org&gt; wrote: > > > > > >> IMO no timezone conversion whatsoever should be applied when > > >> persisting LocalDateTime as it doesn't contain any TZ information. > > >> > > >> If the target column type requires a TZ, it should be set to UTC, > > >> storing the given value without any shift. I.e. the LDT value > > >> 2007-12-03T10:15:30 should be stored as 2007-12-03T10:15:30@UTC, no > > >> matter what's the VM's timezone or any TZ related config. This allows > > >> to retrieve the original value later on, also if e.g. the loading VM > > >> is in a different TZ. > > >> > > >> In fact I'd even recommend to use a string-based column type to store > > >> LDT, as it avoids these kinds of issues altogether. > > >> > > >> --Gunnar > > >> > > >> > > >> > > >> > > >> > > >> Am Fr., 5. Okt. 2018 um 07:15 Uhr schrieb Vlad Mihalcea > > >> <mihalcea.vlad(a)gmail.com&gt;: > > >> > > > >> > Hi, > > >> > While reviewing this Jira issue: > > >> > > > >> > https://hibernate.atlassian.net/browse/HHH-12988 > > >> > > > >> > and further discussing it via Twitter, I wonder if we should persist > > >> > LocalTime as-is without any TimeZone transformation > > >> > that may be done if we enable `hibernate.jdbc.time_zone`? > > >> > > > >> > According to the Date/Time API, LocalTime should not be relative to any > > >> > TimeZone. > > >> > > > >> > If we make this change, it means we need to use a LocalTime SQL > > >> descriptor > > >> > that ignores the jdbc.time_zone property, > > >> > and the change is going to break compatibility as well. > > >> > > > >> > Vlad > > >> > _______________________________________________ > > >> > hibernate-dev mailing list > > >> > hibernate-dev(a)lists.jboss.org > > >> > https://lists.jboss.org/mailman/listinfo/hibernate-dev > > >> _______________________________________________ > > >> hibernate-dev mailing list > > >> hibernate-dev(a)lists.jboss.org > > >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > > >> > > > > > _______________________________________________ > > hibernate-dev mailing list > > hibernate-dev(a)lists.jboss.org > > https://lists.jboss.org/mailman/listinfo/hibernate-dev > > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev

Hi, My question was more about LocalTime, which is much more straightforward to address than LocalDateTime in the context of time zones.

For DateTime types which support timezones, I'll have to study to see what other non-breaking alternatives we may have. Vlad On Fri, Oct 5, 2018 at 2:28 PM Steve Ebersole <steve(a)hibernate.org&gt; wrote: > > Ah good, its time for this yearly discussion :D > > The problem is as Vlad points out. We are kind of forced to do *something* wrt timezone to often times counteract what the JDBC driver will do. As I have always contended, imo that saving a date into the database with any kind of timzone (other than UTC) is simply wrong. I personally would never do it - and I think we've seen enough of the down sides to doing it. As Sanne points out, storing epoch-based dates (Instant, etc) is always the best option > > One cool option would be an AttributeConverter that handles the timezone transformation, combined with telling Hibernate to always use UTC for the JDBC timezone > > > On Fri, Oct 5, 2018 at 4:44 AM Sanne Grinovero <sanne(a)hibernate.org&gt; wrote: >> >> On Fri, 5 Oct 2018 at 10:28, Vlad Mihalcea <mihalcea.vlad(a)gmail.com&gt; wrote: >> > >> > Hi >> > >> > IMO no timezone conversion whatsoever should be applied when >> > > persisting LocalDateTime as it doesn't contain any TZ information. >> > >> > >> > That's not very easy to do since either the JDBC Driver or the database >> > engine might to the timezone conversion based on the underlying setting. >> > >> > In fact I'd even recommend to use a string-based column type to store >> > > LDT, as it avoids these kinds of issues altogether. >> >> +1 I think we need to enforce that. Mapping a Java type which is >> explicitly designed to be insensitive to TZ should not be stored into >> a type which is sensitive to it. That would otherwise defeat the >> purpose of using these specific types. >> >> If people really want to convert stuff, that's business logic. So that >> belong in the business layer: let them do explicit conversion in their >> own code before invoking the setter of an entity, that will also help >> to bring awareness on any conversion issue they might have. >> >> > >> > >> > Actually, a Long (BigInt) column type with the epoch would be more suitable >> > than a CHAR-based column since it's more compact and achieves the same goal. >> > However, many clients will not want to store Date/Time in Int or String >> > columns as explained in the following article: >> >> -1 >> >> let's use Epoch based numerics for Java types which are based on >> Epoch. e.g. java.time.Instant. >> >> People can always transform a date into an epoch-independent number by >> simply encoding it as a number; >> e.g. "2018-10-31" -> 20181031 . I guess it could save you some bytes >> but I'd be skeptical on doing this automatically. >> >> > >> > https://www.periscopedata.com/blog/better-sql-schema >> > >> > Maybe introducing another value for "hibernate.jdbc.time_zone" ("as_is"?) >> > > that would also impact how we map timezone-less types, would be a good idea? >> > >> > >> > We would have to introduce a new configuration property as a strategy where >> > the current behavior is "legacy" >> > while the new one must be explicitly enabled. >> > >> > Vlad >> > >> > On Fri, Oct 5, 2018 at 11:17 AM Yoann Rodiere <yoann(a)hibernate.org&gt; wrote: >> > >> > > > In fact I'd even recommend to use a string-based column type to store >> > > > LDT, as it avoids these kinds of issues altogether. >> > > >> > > Or just, you know, "timestamp without timezone". Where possible. >> > > >> > > More to the point, I agree with Vlad's proposition, and I also think ORM >> > > should avoid messing with timezones as much as possible: when the user >> > > didn't provide one (LocalDate, LocalDateTime, Date), don't store one, when >> > > he provided one (ZonedDateTime, OffsetDateTime, Calendar, ...), store it >> > > exactly as provided. The goal being to return the exact value that was >> > > persisted when later retrieving the data. >> > > But unfortunately I think there is a lot of legacy behaviors that differ >> > > from this, so any attempt at "fixing" it would break compatibility and make >> > > someone angry. >> > > >> > > Maybe introducing another value for "hibernate.jdbc.time_zone" ("as_is"?) >> > > that would also impact how we map timezone-less types, would be a good idea? >> > > >> > > >> > > Yoann Rodière >> > > Hibernate NoORM Team >> > > yoann(a)hibernate.org >> > > >> > > >> > > On Fri, 5 Oct 2018 at 09:38, Gunnar Morling <gunnar(a)hibernate.org&gt; wrote: >> > > >> > >> IMO no timezone conversion whatsoever should be applied when >> > >> persisting LocalDateTime as it doesn't contain any TZ information. >> > >> >> > >> If the target column type requires a TZ, it should be set to UTC, >> > >> storing the given value without any shift. I.e. the LDT value >> > >> 2007-12-03T10:15:30 should be stored as 2007-12-03T10:15:30@UTC, no >> > >> matter what's the VM's timezone or any TZ related config. This allows >> > >> to retrieve the original value later on, also if e.g. the loading VM >> > >> is in a different TZ. >> > >> >> > >> In fact I'd even recommend to use a string-based column type to store >> > >> LDT, as it avoids these kinds of issues altogether. >> > >> >> > >> --Gunnar >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> Am Fr., 5. Okt. 2018 um 07:15 Uhr schrieb Vlad Mihalcea >> > >> <mihalcea.vlad(a)gmail.com&gt;: >> > >> > >> > >> > Hi, >> > >> > While reviewing this Jira issue: >> > >> > >> > >> > https://hibernate.atlassian.net/browse/HHH-12988 >> > >> > >> > >> > and further discussing it via Twitter, I wonder if we should persist >> > >> > LocalTime as-is without any TimeZone transformation >> > >> > that may be done if we enable `hibernate.jdbc.time_zone`? >> > >> > >> > >> > According to the Date/Time API, LocalTime should not be relative to any >> > >> > TimeZone. >> > >> > >> > >> > If we make this change, it means we need to use a LocalTime SQL >> > >> descriptor >> > >> > that ignores the jdbc.time_zone property, >> > >> > and the change is going to break compatibility as well. >> > >> > >> > >> > Vlad >> > >> > _______________________________________________ >> > >> > hibernate-dev mailing list >> > >> > hibernate-dev(a)lists.jboss.org >> > >> > https://lists.jboss.org/mailman/listinfo/hibernate-dev >> > >> _______________________________________________ >> > >> hibernate-dev mailing list >> > >> hibernate-dev(a)lists.jboss.org >> > >> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> > >> >> > > >> > _______________________________________________ >> > hibernate-dev mailing list >> > hibernate-dev(a)lists.jboss.org >> > https://lists.jboss.org/mailman/listinfo/hibernate-dev >> >> _______________________________________________ >> hibernate-dev mailing list >> hibernate-dev(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev