[hibernate-dev] 6.0 - HQL literals

[Steve Ebersole]

Actually Java and JDBC say that all of these Java 8 temporal types are
valid to pass via JDBC.  It's done "implicitly" via the various
`#setObject` methods (as opposed to `#setTimestamp` e.g.).

Part of the problem here (6 and before) at the moment is that our
LocalDateTimeType, ZonedDateTimeType, etc types use the JDBC Time, Date and
Timestamp SQL mappings rather than SQL type descriptors that simply use
`#setObject`


On Wed, Jan 8, 2020 at 5:57 AM Yoann Rodiere <yoann at hibernate.org> wrote:

> > Can't we just render a literal in the DBMS proprietary way as literal
> again
> > but with respective TZ information to avoid TZ issues in the JDBC driver?
>
> This may be an option. We would need to take into account the default JVM
> timezone, or the "jdbc_timezone" setting, to set the correct offset.
> However, I believe some JDBC drivers (MariaDB?) have options to convert
> between the JVM timezone and the DB timezone, because apparently some DBs
> assume the timezone of date/time values to be something else than UTC. Not
> sure SQL literals would be interpreted correctly in such scenarios.
>
>
> Yoann Rodière
> Hibernate Team
> yoann at hibernate.org
>
>
> On Wed, 8 Jan 2020 at 12:41, Christian Beikov <christian.beikov at gmail.com>
> wrote:
>
> > Can't we just render a literal in the DBMS proprietary way as literal
> again
> > but with respective TZ information to avoid TZ issues in the JDBC driver?
> >
> > If we use an instant literal we use the UTC TZ in a SQL literal or
> function
> > to render that.
> >
> > Yoann Rodiere <yoann at hibernate.org> schrieb am Mi., 8. Jän. 2020, 11:22:
> >
> > > > This does nothing with Type.  The way the grammar is defined it
> > literally
> > > understands each piece of the temporal.  So given, e.g., {2020-01-01},
> we
> > > know that 2020 is the year, etc.  This is the benefit of defining it
> > > syntactically.
> > >
> > > I trust you can build a temporal correctly from a string. I'm more
> > > concerned about passing that information to the JDBC driver through a
> > > parameter, or even directly to the database through an SQL literal.
> Last
> > > time I checked you had to use java.sql types to pass temporal
> parameters
> > to
> > > JDBC drivers, so you will have to convert the java.time value to a
> > > java.sql.Timestamp or similar eventually. And *that* is much more
> tricky
> > > that I, at least, originally thought.
> > >
> > > Among other quirks:
> > >
> > >    - creating a Timestamp from a year/day/etc. assumes the given
> > >    year/day/etc. are in the default JVM timezone.
> > >    - the JDBC driver will sometimes extract the year/day/etc. and
> > interpret
> > >    them as being in the DB timezone, or will sometimes use a DateFormat
> > > with a
> > >    timezone to convert it to the correct timezone. It depends on the
> > driver
> > >    and even the version of the driver.
> > >    - java.sql.Timestamp and java.time do not rely on the same calendar:
> > >    Julian/Gregorian calendar for one, proleptic Gregorian calendar for
> > the
> > >    other.
> > >    - java.sql.Timestamp and java.time do not assume the same offsets
> for
> > >    various zone IDs around and before 1900, when time zones were not a
> > >    formalized concept.
> > >
> > > I've spent days on conversion problems between java.time and java.sql
> in
> > > ORM over the last few months.
> > >
> > > Which is why I think using LocalDateTimeType to convert between the
> > > LocalDateTime literal and the Timestamp would be a good idea. If you
> want
> > > to rewrite that code for literals, sure that can work, but exhaustive
> > > testing will be needed.
> > >
> > > > As counter-intuitive as it sounds, a ZonedDateTime actually includes
> an
> > > offset to differentiate the overlap case you mention.
> > >
> > > Yep. That's why it accepts parsing a ZoneDateTime with both a zone ID
> and
> > > an offset. Try this:
> > > https://gist.github.com/yrodiere/278996f865a9854e222aea58b5a7f26e
> > >
> > > Note that a bug affects parsing ZoneDateTimes with both offset and zone
> > ID
> > > on JDK8 (fixed in 9): https://bugs.openjdk.java.net/browse/JDK-8066982
> > > We have a helper to work around that in Search:
> > >
> > >
> >
> https://github.com/hibernate/hibernate-search/blob/334e4aad5c776151bcf5dbb6d27bf61fc8a93443/util/common/src/main/java/org/hibernate/search/util/common/impl/TimeHelper.java#L38
> > >
> > > > I think the confusion here is in terms of (1) recognizing a temporal
> > > literal and "parsing it" and (2) applying it to SQL.  Different parts
> of
> > > the puzzle.
> > >
> > > Yep.
> > >
> > > Yoann Rodière
> > > Hibernate Team
> > > yoann at hibernate.org
> > >
> > >
> > > On Tue, 7 Jan 2020 at 19:50, Steve Ebersole <steve at hibernate.org>
> wrote:
> > >
> > > > As far as I know, even Java does not support that.  A true zone-id
> > would
> > > >> be something like (for me) "America/Chicago".  If I ask Java to
> parse
> > > >> "2020-01-01 10:10:10 America/Chicago +02:00" it just says no.  For
> me,
> > > CST
> > > >> (standard) and CDT (daylight savings) are really synonyms for
> offset -
> > > >> either UTC-05:00 or UTC-06:00 depending on day of the year.
> > > >>
> > > >
> > > > It seems like the proper syntax for that would actually be
> "2020-01-01
> > > > 10:10:10+02:00 America/Chicago", but in my
> > > > testing DateTimeFormatter#parseBest did not handle that form either
> > > >
> > > >
> > > _______________________________________________
> > > 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