MySQL DATETIME fractional seconds

MySQL 5.6.4 TIME and DATETIME types support fractions of seconds that can be used to store DATETIME HOUR TO FRACTION(N) or DATETIME YEAR TO FRACTION(N).

Before Genero 3.00, the Oracle® MySQL driver converted DATETIME types as follows:
  • DATETIME HOUR TO SECOND was converted to MySQL TIME.
  • Other DATETIME types were converted to MySQL DATETIME.

Starting with Genero 3.00, when creating a table in a BDL program with the CREATE TABLE statement, if the MySQL server version is greater or equal to 5.6.4, the types are converted differently, as follows:

The SQL Translator of the MySQL driver makes the following conversions automatically for the DATETIME types:
  • DATETIME HOUR TO MINUTE is converted to MySQL TIME (seconds set to 00).
  • DATETIME HOUR TO SECOND is converted to MySQL TIME.
  • DATETIME HOUR TO FRACTION(n) is converted to MySQL TIME(n).
  • DATETIME YEAR TO MINUTE is converted to MySQL DATETIME (seconds set to 00).
  • DATETIME YEAR TO SECOND is converted to MySQL DATETIME.
  • DATETIME YEAR TO FRACTION(n) is converted to MySQL DATETIME(n).

This change has no impact if your application is using DATETIME HOUR TO SECOND or DATETIME YEAR TO SECOND. However, it is now possible to store DATETIME HOUR TO FRACTION(N) and DATETIME YEAR TO FRACTION(N) data. The DATETIME YEAR TO FRACTION(N) is typically used to implement data modification timestamps to track user changes.