On Tue, Jan 7, 2020 at 10:05 AM Yoann Rodiere <yo...@hibernate.org> wrote:
> On Tue, 7 Jan 2020 at 14:45, Steve Ebersole <st...@hibernate.org> wrote: > >> Sorry, I should have been more clear. The literals are not "passed >> through"; it's just a mechanism to be able to recognize the literal >> syntactically while parsing. All of those forms I showed actually are >> handled in the code and interpreted as a Java temporal. We then do >> whatever we want to do with it in order to send it to the database (often >> even as a parameter). >> > > You mean you use the org.hibernate.type.Type instance registered in the > dialect for this Java type to convert it to the SQL type? Nice, that > definitely sounds more robust than what I thought. > 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 suppose the org.hibernate.type.Type instance used is inferred from the > class of the litteral, which should be good enough in most cases. It may > not work properly when users assigned their own type to a property, for > example "where foo = {d 2020-01-01}" where "foo" is a LocalDate mapped as a > Timestamp (see org.hibernate.test.type.LocalDateTest. > DateAsTimestampRemappingH2Dialect). But that's rather advanced use case, > and if you provide a way to define custom literal types as you said, users > will have a way out. > This ties in with what I mentioned above. The literal value is always an instance of a Java 8 temporal type. The Type we associate with the node will be pulled from the TypeConfiguration. Be aware that the form zone id + offset may also make sense, when users > want to use a zone id during DST switch with overlap (the same datetime > happens twice in the same zone, so the offset is needed for disambiguation). > I suppose the offset alone would be enough, but from what I've seen, the > resulting ZoneDateTime object is different depending on whether you pass > zone id + offset or just offset. > 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. As counter-intuitive as it sounds, a ZonedDateTime actually includes an offset to differentiate the overlap case you mention. > > > * We also support a STRING_LITERAL form of temporal literals as I >> mentioned originally. In my experience, using >> `java.time.format.DateTimeFormatter#parseBest` always returned a >> ZonedDateTime whether a zone-id or offset was specified. My understanding >> is that this varies from Java 8 to Java 9. So that's something to consider >> as well. >> > > Not sure I see which literal you're talking about, since the ones you > mentioned were temporal literals; do you mean something like "where date = > '2019-01-01'"? > If it's new, I'd personally be in favor of not allowing this and sticking > to a specific syntax for time literals. Seems less error-prone. > No I meant the alternative form I mentioned initially. So I could have `{ts '2020-0101 10:10:10'}` or `{2020-0101 10:10:10`). The first form is parsed from the String via `java.time.format.DateTimeFormatter#parseBest`. For the other form, we actually process each int value individually and piece together the correct temporal. I was talking about the org.hibernate.type.Type implementations for all the > Java date/time types, be it in java.util or in java.time. As you know, each > type maps to the database column slightly differently, and each has its > quirks, because of how JDBC drivers handle date/time types. Quite a few > JDBC drivers behave very strangely, especially in corner cases (DST switch, > dates before year 1900, ...). Often, the legacy date/time APIs are to blame > (though I wouldn't touch the older versions of the MySQL drivers with a ten > foot pole). > > More to the point: I know from experience it is quite hard to get the > conversion from Java date/time values to an SQL date/time value to work > properly in all cases (JDBC driver, JVM timezone, database timezone). But > if you re-use org.hibernate.type.Type to convert literals to SQL types, > then it should behave the same as when persisting, so queries such as > "where foo = {2019-01-01}" should work even if the date is converted in a > convoluted way before it is sent to the database. > You can find examples in package org.hibernate.test.type: LocalTimeTest, > LocalDateTest, ... > > >> >> I might be wrong, but only exhaustive testing of all literals with all >>> date/time types on all RDBMS will let us know for sure. Let's keep in mind >>> how many bugs have surfaced from time-related features in the past... >>> >> >> Gavin actually did quite a bit of that in the PR he sent us. He added >> pretty cool support for various temporal-related things such as how to >> handle formatting (to_char, etc), extraction and temporal arithmetic - >> specifically formalizing and normalizing them across databases. >> > > Nice. As long as it uses org.hibernate.type.Type implementations when it > comes to converting between Java values and SQL values, it should be safe. > > 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. HTH _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev