What java.sql.Date does is wrong for many reasons (mostly because it extends java.util.Date which is a horrible API), but I think Vlad laid it out pretty well why it's a JDBC driver configuration problem and not a Hibernate problem. If you simply rely on the Java API to use handle it's l own TZ conversion without telling it any zones (which that JDBC URL tells it), the problem should go away. The driver does try to read the server's time-zone, though, so maybe using `useTimezone=false` in the JDBC URL is the right solution, but it certainly shouldn't be to try and appease every driver's individual quirks. Even MySQL's own API works under the assumption that LocalDate should be converted using the JVM's default time-zone. https://github.com/mysql/mysql-connector-j/blob/release/5.1/src/com/mysql... On 14 March 2017 at 09:38, Christian Beikov <christian.beikov(a)gmail.com&gt; wrote: > Hey everyone, > > I'd like to get your opinion on something. We had this issues regarding > timezone handling which Vlad closed pretty quickly saying it isn't a > Hibernate problem and I generally agree, but still would like to know > what you think about it. > > So basically what we do in the LocalDateJavaDescriptor(and also in some > other places) is to use the value returned by java.sql.Date in some way > and pack that into the target type. In this case this happens behind the > scenes when invoking java.sql.Date.toLocalDate() but internally it just > calls LocalDate.of(getYear() + 1900, getMonth() + 1, getDate()). > > Now IMO the problem really is in java.sql.Date because it does a > timezone conversion. The user who created the issue HHH-11396 > <https://hibernate.atlassian.net/browse/HHH-11396> pointed out he was > using a DATE column type because he wanted to have a simple date i.e. > year, month and date. When using java.sql.Date and the consumer is in a > timezone after UTC i.e. UTC+1, the calculations of java.sql.Date will > subtract that offset during /normalization/ and the millisecond value > will change by that offset. This means that a date in the DBMS that is > 2000-01-01 will become 1999-12-31 when the client is in UTC+1. > > One possible fix is to simply configure UTC for the consumer, then there > will be no timezone shift. > > I think what java.sql.Date does is wrong because a date has no time > part, so there shouldn't be any time shifts. We should workaround that > by shifting the millisecond value back when constructing a LocalDate. > > What do you think should we do? Does anyone maybe know why java.sql.Date > behaves that way? > > > -- > > Mit freundlichen Grüßen, > ------------------------------------------------------------------------ > *Christian Beikov* > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev

The driver just has to use the available `getDate/getMonth/getYear` functions of the java.sql.Date class, because they're what's intended by the API. The problem is that the MySQL driver tries to do more than that, when their database doesn't even support time zones. On 14 March 2017 at 12:17, Christian Beikov <christian.beikov(a)gmail.com&gt; wrote: > So you are saying that it should be the JDBC drivers responsibility to > add the JVMs local timezone offset to the java.sql.Date so the > normalization can subtract the offset again? I would be ok with that, > but is it really the responsibility of the JDBC driver? > > > Mit freundlichen Grüßen, > ------------------------------------------------------------------------ > *Christian Beikov* > Am 14.03.2017 um 10:39 schrieb Jordan Gigov: >> What java.sql.Date does is wrong for many reasons (mostly because it >> extends java.util.Date which is a horrible API), but I think Vlad laid >> it out pretty well why it's a JDBC driver configuration problem and >> not a Hibernate problem. If you simply rely on the Java API to use >> handle it's l own TZ conversion without telling it any zones (which >> that JDBC URL tells it), the problem should go away. The driver does >> try to read the server's time-zone, though, so maybe using >> `useTimezone=false` in the JDBC URL is the right solution, but it >> certainly shouldn't be to try and appease every driver's individual >> quirks. >> >> Even MySQL's own API works under the assumption that LocalDate should >> be converted using the JVM's default time-zone. >> https://github.com/mysql/mysql-connector-j/blob/ release/5.1/src/com/mysql/jdbc/JDBC42Helper.java#L50 >> >> On 14 March 2017 at 09:38, Christian Beikov <christian.beikov(a)gmail.com&gt; wrote: >>> Hey everyone, >>> >>> I'd like to get your opinion on something. We had this issues regarding >>> timezone handling which Vlad closed pretty quickly saying it isn't a >>> Hibernate problem and I generally agree, but still would like to know >>> what you think about it. >>> >>> So basically what we do in the LocalDateJavaDescriptor(and also in some >>> other places) is to use the value returned by java.sql.Date in some way >>> and pack that into the target type. In this case this happens behind the >>> scenes when invoking java.sql.Date.toLocalDate() but internally it just >>> calls LocalDate.of(getYear() + 1900, getMonth() + 1, getDate()). >>> >>> Now IMO the problem really is in java.sql.Date because it does a >>> timezone conversion. The user who created the issue HHH-11396 >>> <https://hibernate.atlassian.net/browse/HHH-11396> pointed out he was >>> using a DATE column type because he wanted to have a simple date i.e. >>> year, month and date. When using java.sql.Date and the consumer is in a >>> timezone after UTC i.e. UTC+1, the calculations of java.sql.Date will >>> subtract that offset during /normalization/ and the millisecond value >>> will change by that offset. This means that a date in the DBMS that is >>> 2000-01-01 will become 1999-12-31 when the client is in UTC+1. >>> >>> One possible fix is to simply configure UTC for the consumer, then there >>> will be no timezone shift. >>> >>> I think what java.sql.Date does is wrong because a date has no time >>> part, so there shouldn't be any time shifts. We should workaround that >>> by shifting the millisecond value back when constructing a LocalDate. >>> >>> What do you think should we do? Does anyone maybe know why java.sql.Date >>> behaves that way? >>> >>> >>> -- >>> >>> Mit freundlichen Grüßen, >>> ------------------------------------------------------------ ------------ >>> *Christian Beikov* >>> _______________________________________________ >>> 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

I'm not sure exactly where the fault lies (java.util versus java.sql), but we should certainly help hide that if we reasonably can. But conceptually, milliseconds on a DATE (no time) makes zero sense at all. So where does the milliseconds come from? It is also possible that the db is (incorrectly IMO) storing millisecond portion of a DATE value. In your example Christian, is the driver returning to us a DATE reference will a non-zero millisecond portion? And if so, does the outcome change if we reset the Date's milliseconds to zero before we call toLocalDate()? If so, maybe the correct solution (certainly the easiest) is to simply set the Date's milliseconds value to zero whenever we read them from the db. IIRC I think I did change that once and people complained, but I forget the details - I tried searching Jira but could not find it. Like I said, IMO that is conceptually correct anyway. On Wed, Mar 15, 2017 at 2:03 AM Vlad Mihalcea <mihalcea.vlad(a)gmail.com&gt; wrote: > Hi, > > I recently helped one of our users with a MySQL timezone issue, and I > realized that the default legacy timezone handling is to be avoided. > > Basically, you want to set this "useLegacyDatetimeCode' configuration > property to false. > > for more details, check out this article: > > > https://vladmihalcea.com/2017/03/14/how-to-store-date-time-and-timestamps... > > However, for LocalDate, I'll have to think it more thoroughly to see if > there is something we could do to address the problem raised by Christian > even if it implies adding a section to our User Guide to help users set the > driver properly to avoid some possible issues. > > Vlad > > On Tue, Mar 14, 2017 at 12:40 PM, Jordan Gigov <coladict(a)gmail.com&gt; wrote: > >> The driver just has to use the available `getDate/getMonth/getYear` >> functions of the java.sql.Date class, because they're what's intended >> by the API. The problem is that the MySQL driver tries to do more than >> that, when their database doesn't even support time zones. >> >> On 14 March 2017 at 12:17, Christian Beikov <christian.beikov(a)gmail.com&gt; >> wrote: >>> So you are saying that it should be the JDBC drivers responsibility to >>> add the JVMs local timezone offset to the java.sql.Date so the >>> normalization can subtract the offset again? I would be ok with that, >>> but is it really the responsibility of the JDBC driver? >>> >>> >>> Mit freundlichen Grüßen, >>> > ------------------------------------------------------------------------ >>> *Christian Beikov* >>> Am 14.03.2017 um 10:39 schrieb Jordan Gigov: >>>> What java.sql.Date does is wrong for many reasons (mostly because it >>>> extends java.util.Date which is a horrible API), but I think Vlad laid >>>> it out pretty well why it's a JDBC driver configuration problem and >>>> not a Hibernate problem. If you simply rely on the Java API to use >>>> handle it's l own TZ conversion without telling it any zones (which >>>> that JDBC URL tells it), the problem should go away. The driver does >>>> try to read the server's time-zone, though, so maybe using >>>> `useTimezone=false` in the JDBC URL is the right solution, but it >>>> certainly shouldn't be to try and appease every driver's individual >>>> quirks. >>>> >>>> Even MySQL's own API works under the assumption that LocalDate should >>>> be converted using the JVM's default time-zone. >>>> https://github.com/mysql/mysql-connector-j/blob/ >> release/5.1/src/com/mysql/jdbc/JDBC42Helper.java#L50 >>>> On 14 March 2017 at 09:38, Christian Beikov < > christian.beikov(a)gmail.com&gt; >> wrote: >>>>> Hey everyone, >>>>> >>>>> I'd like to get your opinion on something. We had this issues > regarding >>>>> timezone handling which Vlad closed pretty quickly saying it isn't a >>>>> Hibernate problem and I generally agree, but still would like to know >>>>> what you think about it. >>>>> >>>>> So basically what we do in the LocalDateJavaDescriptor(and also in > some >>>>> other places) is to use the value returned by java.sql.Date in some > way >>>>> and pack that into the target type. In this case this happens behind >> the >>>>> scenes when invoking java.sql.Date.toLocalDate() but internally it > just >>>>> calls LocalDate.of(getYear() + 1900, getMonth() + 1, getDate()). >>>>> >>>>> Now IMO the problem really is in java.sql.Date because it does a >>>>> timezone conversion. The user who created the issue HHH-11396 >>>>> <https://hibernate.atlassian.net/browse/HHH-11396> pointed out he > was >>>>> using a DATE column type because he wanted to have a simple date i.e. >>>>> year, month and date. When using java.sql.Date and the consumer is > in a >>>>> timezone after UTC i.e. UTC+1, the calculations of java.sql.Date will >>>>> subtract that offset during /normalization/ and the millisecond value >>>>> will change by that offset. This means that a date in the DBMS that > is >>>>> 2000-01-01 will become 1999-12-31 when the client is in UTC+1. >>>>> >>>>> One possible fix is to simply configure UTC for the consumer, then >> there >>>>> will be no timezone shift. >>>>> >>>>> I think what java.sql.Date does is wrong because a date has no time >>>>> part, so there shouldn't be any time shifts. We should workaround > that >>>>> by shifting the millisecond value back when constructing a LocalDate. >>>>> >>>>> What do you think should we do? Does anyone maybe know why >> java.sql.Date >>>>> behaves that way? >>>>> >>>>> >>>>> -- >>>>> >>>>> Mit freundlichen Grüßen, >>>>> ------------------------------------------------------------ >> ------------ >>>>> *Christian Beikov* >>>>> _______________________________________________ >>>>> 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 _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev