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

[Gail Badner]

To clarify, HHH-8401 is to support MySQL fractional seconds; HHH-9444 is to fix test failures due MySQL's new support for fractional seconds.

----- Original Message -----
> From: "Gail Badner" <gbadner at redhat.com>
> To: "Hibernate Dev" <hibernate-dev at lists.jboss.org>
> 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