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(a)redhat.com>
To: "Hibernate Dev" <hibernate-dev(a)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