You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by Mark Struberg <st...@yahoo.de.INVALID> on 2018/10/30 11:58:26 UTC
how to handle Date fractions in OpenJPA DBDictionary?
Hi folks!
While trying to fix our tests on MySQL I figured that a few tests randomly fail because they do a Date now = new Date(); and then store it into the db and search it again.
E.g. in TestExplicitAccess#
https://github.com/apache/openjpa/blob/08bb3a5a0e7573622aa6dc25763cb6513095093b/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/access/TestExplicitAccess.java#L479
now is e.g. 2108-10-30 10:01:01.588 and after writing it into the DB it gets rounded up to 2108-10-30 10:01:02
Doing the query seems to not round up. So the query is 'WHERE created='2108-10-30 10:01:01'.
And of course then it cannot find anything.
There are a few issues in here.
a.) why don't we also round up to the same value when the Date get's passed as a parameter to the query but DOES get rounded when persisting the entity?
Do we want this? Does it cause backward incompatibility if we introduce this? Does it cause unnecessary overhead?
We also would need to dig into whether this is our problem at all or whether this rounding happens in the JDBC driver.
b.) Why the hell does MySQL not support milliseconds or nanoseconds out of the box for any of it's DATETIME or TIMESTAMP? :)
Did a bit of research (man that takes time to grab all those bits). Since mysql-5.6.4 one can now add a fraction part.
Thanks to Robert Panzer (@bobbytank42) for the tip and link! https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html
Now one can write DATETIME(3) which would fit the java.util.Date accuracy or DATETIME(6) to fit java.sql.Date (nanoseconds).
Note that _before_ MySQL-5.3 the format DATE(n) did also exist but meant something else! Up to 5.3 this denoted the number of digits to view from the date.
e.g. DATETIME(8) did probably only output date and the hour in a standard query.
What to do with that situation?
Say we have an existing @TemporalType(TIMESTAMP) java.util.Date column which got generated as DATETIME (so just up to seconds accuracy, no further fractions) and now switch this to DATETIME(3)? Would we do an for a java.sql.Date? DATETIME(6)?
How would we want to handle this fraction information? With DBDictionary#appendSize ?
Currently DATETIME and TIMESTAMP are both added to fixedSizeTypeNameSet.
Plus: is there already a way to deal with different versions of MySQL?
Feel like I'm quite rusty in this area ;)
LieGrue,
strub
Re: how to handle Date fractions in OpenJPA DBDictionary?
Posted by Mark Struberg <st...@yahoo.de.INVALID>.
Btw, figured the rounding happens in the MySQL JDBC Driver :(
So it'not really our own bug but rather a mysql glitch.
LieGrue,
strub
> Am 30.10.2018 um 14:12 schrieb Romain Manni-Bucau <rm...@gmail.com>:
>
> Think I saw some products doing MySQL55Dictionary, MySQL56Dictionary
> pattern, think we can just ask the driver the version when guessing the
> dictionary is not explicit and if explicit we can just have a mysqlVersion
> configuration in the "properties" (mysql(mysqlVersion=5.6)).
>
> wdyt?
>
> Romain Manni-Bucau
> @rmannibucau <https://twitter.com/rmannibucau> | Blog
> <https://rmannibucau.metawerx.net/> | Old Blog
> <http://rmannibucau.wordpress.com> | Github <https://github.com/rmannibucau> |
> LinkedIn <https://www.linkedin.com/in/rmannibucau> | Book
> <https://www.packtpub.com/application-development/java-ee-8-high-performance>
>
>
> Le mar. 30 oct. 2018 à 12:59, Mark Struberg <st...@yahoo.de.invalid> a
> écrit :
>
>> Hi folks!
>>
>> While trying to fix our tests on MySQL I figured that a few tests randomly
>> fail because they do a Date now = new Date(); and then store it into the db
>> and search it again.
>> E.g. in TestExplicitAccess#
>>
>> https://github.com/apache/openjpa/blob/08bb3a5a0e7573622aa6dc25763cb6513095093b/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/access/TestExplicitAccess.java#L479
>>
>> now is e.g. 2108-10-30 10:01:01.588 and after writing it into the DB it
>> gets rounded up to 2108-10-30 10:01:02
>> Doing the query seems to not round up. So the query is 'WHERE
>> created='2108-10-30 10:01:01'.
>> And of course then it cannot find anything.
>>
>> There are a few issues in here.
>>
>> a.) why don't we also round up to the same value when the Date get's
>> passed as a parameter to the query but DOES get rounded when persisting the
>> entity?
>> Do we want this? Does it cause backward incompatibility if we introduce
>> this? Does it cause unnecessary overhead?
>> We also would need to dig into whether this is our problem at all or
>> whether this rounding happens in the JDBC driver.
>>
>>
>> b.) Why the hell does MySQL not support milliseconds or nanoseconds out of
>> the box for any of it's DATETIME or TIMESTAMP? :)
>> Did a bit of research (man that takes time to grab all those bits). Since
>> mysql-5.6.4 one can now add a fraction part.
>> Thanks to Robert Panzer (@bobbytank42) for the tip and link!
>> https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html
>> Now one can write DATETIME(3) which would fit the java.util.Date accuracy
>> or DATETIME(6) to fit java.sql.Date (nanoseconds).
>>
>> Note that _before_ MySQL-5.3 the format DATE(n) did also exist but meant
>> something else! Up to 5.3 this denoted the number of digits to view from
>> the date.
>> e.g. DATETIME(8) did probably only output date and the hour in a standard
>> query.
>>
>> What to do with that situation?
>> Say we have an existing @TemporalType(TIMESTAMP) java.util.Date column
>> which got generated as DATETIME (so just up to seconds accuracy, no further
>> fractions) and now switch this to DATETIME(3)? Would we do an for a
>> java.sql.Date? DATETIME(6)?
>>
>> How would we want to handle this fraction information? With
>> DBDictionary#appendSize ?
>> Currently DATETIME and TIMESTAMP are both added to fixedSizeTypeNameSet.
>>
>> Plus: is there already a way to deal with different versions of MySQL?
>>
>> Feel like I'm quite rusty in this area ;)
>>
>> LieGrue,
>> strub
>>
>>
Re: how to handle Date fractions in OpenJPA DBDictionary?
Posted by Romain Manni-Bucau <rm...@gmail.com>.
Think I saw some products doing MySQL55Dictionary, MySQL56Dictionary
pattern, think we can just ask the driver the version when guessing the
dictionary is not explicit and if explicit we can just have a mysqlVersion
configuration in the "properties" (mysql(mysqlVersion=5.6)).
wdyt?
Romain Manni-Bucau
@rmannibucau <https://twitter.com/rmannibucau> | Blog
<https://rmannibucau.metawerx.net/> | Old Blog
<http://rmannibucau.wordpress.com> | Github <https://github.com/rmannibucau> |
LinkedIn <https://www.linkedin.com/in/rmannibucau> | Book
<https://www.packtpub.com/application-development/java-ee-8-high-performance>
Le mar. 30 oct. 2018 à 12:59, Mark Struberg <st...@yahoo.de.invalid> a
écrit :
> Hi folks!
>
> While trying to fix our tests on MySQL I figured that a few tests randomly
> fail because they do a Date now = new Date(); and then store it into the db
> and search it again.
> E.g. in TestExplicitAccess#
>
> https://github.com/apache/openjpa/blob/08bb3a5a0e7573622aa6dc25763cb6513095093b/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/access/TestExplicitAccess.java#L479
>
> now is e.g. 2108-10-30 10:01:01.588 and after writing it into the DB it
> gets rounded up to 2108-10-30 10:01:02
> Doing the query seems to not round up. So the query is 'WHERE
> created='2108-10-30 10:01:01'.
> And of course then it cannot find anything.
>
> There are a few issues in here.
>
> a.) why don't we also round up to the same value when the Date get's
> passed as a parameter to the query but DOES get rounded when persisting the
> entity?
> Do we want this? Does it cause backward incompatibility if we introduce
> this? Does it cause unnecessary overhead?
> We also would need to dig into whether this is our problem at all or
> whether this rounding happens in the JDBC driver.
>
>
> b.) Why the hell does MySQL not support milliseconds or nanoseconds out of
> the box for any of it's DATETIME or TIMESTAMP? :)
> Did a bit of research (man that takes time to grab all those bits). Since
> mysql-5.6.4 one can now add a fraction part.
> Thanks to Robert Panzer (@bobbytank42) for the tip and link!
> https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html
> Now one can write DATETIME(3) which would fit the java.util.Date accuracy
> or DATETIME(6) to fit java.sql.Date (nanoseconds).
>
> Note that _before_ MySQL-5.3 the format DATE(n) did also exist but meant
> something else! Up to 5.3 this denoted the number of digits to view from
> the date.
> e.g. DATETIME(8) did probably only output date and the hour in a standard
> query.
>
> What to do with that situation?
> Say we have an existing @TemporalType(TIMESTAMP) java.util.Date column
> which got generated as DATETIME (so just up to seconds accuracy, no further
> fractions) and now switch this to DATETIME(3)? Would we do an for a
> java.sql.Date? DATETIME(6)?
>
> How would we want to handle this fraction information? With
> DBDictionary#appendSize ?
> Currently DATETIME and TIMESTAMP are both added to fixedSizeTypeNameSet.
>
> Plus: is there already a way to deal with different versions of MySQL?
>
> Feel like I'm quite rusty in this area ;)
>
> LieGrue,
> strub
>
>