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
