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
_______________________________________________
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
