Re: [hibernate-dev] MySQL fractional second precision (HHH-8401, HHH-9444)

From: "Gail Badner" <gbadner(a)redhat.com&gt; To: "Hibernate Dev" <hibernate-dev(a)lists.jboss.org&gt; Sent: Tuesday, November 18, 2014 1:59:26 PM Subject: MySQL fractional second precision (HHH-9444) MySQL support for fractional seconds in temporal values is documented in [1]: Prior to MySQL 5.6.4, when storing a value into a column of any temporal data type, fractional part is discarded (truncated). When a column is defined as TIMESTAMP(N), N indicates display width rather than fractional seconds. MySQL 5.6.4 and up allows enabling fractional second support by defining a column like: TIME(n), DATETIME(n), TIMESTAMP(n), where 0 <= n <= 6. A value of 0 indicates no fractional seconds. For compatibiliby with previous MySQL versions, if no value for n is provided, then 0 is the default. This is inconsistent with standard SQL, which has a default of 6. In addition, inserting a temporal value in a column will result in rounding if the column is defined with fewer fractional digits than the value. Starting from MySQL 5.6.4, there are also differences in functions that involve temporal values. MySQL functions like NOW(), CURTIME(), SYSDATE(), or UTC_TIMESTAMP() can optionally take an argument for fractional seconds precision as in NOW(n), CURTIME(n), SYSDATE(n), or UTC_TIMESTAMP(n), where 0 <= n <= 6. For compatibiliby with previous MySQL versions, if no value for n is provided, then 0 is the default. I'm working on a new dialect to support fractional seconds. Since the support for fractional seconds began in MySQL 5.6.4 it kind of complicates naming. I assume MySQL5InnoDBDialect needs to be extended for the InnoDB engine. Is "MySQL564InnoDBDialect" too ugly? Is there any need to have a new dialect for other (non-InnoDB) MySQL engines (e.g., MySQL564DBDialect that extends MySQL5Dialect)? IIUC, to be consistent with the SQL standard the default for the temporal types in the new dialect(s) should be: registerColumnType( Types.TIMESTAMP, "datetime(6)" ); registerColumnType( Types.TIME, "time(6)" ); Is there a need the new dialect(s) to allow an application to define the number of fractional seconds to anything other than 6? If so, would it work to also add the following? registerColumnType( Types.TIMESTAMP, 6, "datetime($l)" ); registerColumnType( Types.TIME, 6, "time($l)" ); Also, IMO, Hibernate should render NOW(), CURTIME(), SYSDATE(), UTC_TIMESTAMP() as NOW(6), CURTIME(6), SYSDATE(6), or UTC_TIMESTAMP(6), respectively, unless an argument is specifically provided. As far as I can tell, Hibernate does not use MySQL's TIMESTAMP column type, so I don't think anything needs to be done to support fractional seconds for that type, or am I missing something here? Comments? Thanks, Gail [1] http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html [2] https://hibernate.atlassian.net/browse/HHH-9444