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 <rmannibu...@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 <strub...@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 >> >>
