[hibernate-dev] HHH-11396 - Timezone handling

[Vlad Mihalcea]

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-in-utc-time-zone-with-jdbc-and-hibernate/

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 at gmail.com> 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 at gmail.com>
> 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 at gmail.com>
> 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 at lists.jboss.org
> >>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >
> > _______________________________________________
> > hibernate-dev mailing list
> > hibernate-dev at lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>