| Bug #69714 | Inconsistent DATETIME conversions with fractional seconds | ||
|---|---|---|---|
| Submitted: | 10 Jul 2013 16:42 | Modified: | 27 Mar 2014 21:21 |
| Reporter: | Todd Farmer (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.6.11 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[10 Jul 2013 17:13]
Todd Farmer
Further documentation rendered incompatible with 5.6 behavior: " Values specified as numbers should be 6, 8, 12, or 14 digits long. If a number is 8 or 14 digits long, it is assumed to be in YYYYMMDD or YYYYMMDDHHMMSS format and that the year is given by the first 4 digits. If the number is 6 or 12 digits long, it is assumed to be in YYMMDD or YYMMDDHHMMSS format and that the year is given by the first 2 digits. Numbers that are not one of these lengths are interpreted as though padded with leading zeros to the closest length. Values specified as nondelimited strings are interpreted according their length. For a string 8 or 14 characters long, the year is assumed to be given by the first 4 characters. Otherwise, the year is assumed to be given by the first 2 characters. The string is interpreted from left to right to find year, month, day, hour, minute, and second values, for as many parts as are present in the string."
[10 Jul 2013 17:33]
Todd Farmer
I think that introducing fractional second support without delimiters is problematic and will lead to ambiguous behavior. For example: mysql> SELECT TIMESTAMP '1307100102031234'; +------------------------------+ | TIMESTAMP '1307100102031234' | +------------------------------+ | 1307-10-01 02:03:12.34 | +------------------------------+ 1 row in set (0.00 sec) The above could easily be meant to be parsed as "13-07-10T01:02:03.1234". I suggest that fractional seconds *always* require a delimiter (either period or comma, neither of which should be allowed as delimiters elsewhere). The parsing of the non-fractional components should align with existing documented behavior.
[27 Mar 2014 21:21]
Paul DuBois
Noted in 5.6.17, 5.7.4 changelogs. A temporal literal string without delimiters and more than 14 digits was validated as a TIMESTAMP/DATETIME value with a two-digit precision fractional seconds part. But fractional seconds should always be separated from other parts of a time by a decimal point.
[29 Mar 2014 8:19]
Laurynas Biveinis
5.6$ bzr log -r 5830
------------------------------------------------------------
revno: 5830
committer: mithun <mithun.c.y@oracle.com>
branch nick: mysql-5.6
timestamp: Thu 2014-02-20 16:59:59 +0530
message:
Bug #17080703: INCONSISTENT DATETIME CONVERSIONS WITH
FRACTIONAL SECONDS
ISSUE :
-------
A temporal literal string without delimiters and having > 14
digits was validated as a TIMESTAMP/DATETIME value with two
digit precision fractional seconds. For example, a 16 digit
string 'xxxxxxxxxxxxxxxx' is interpreted as
'XXXX-XX-XX XX:XX:XX.xx'. But fractional seconds should
always be separated from other parts of time. And, decimal
point is the only delimiter which separates the fractional
seconds from rest.
SOLUTION :
----------
Reject all time typed strings where the fractional seconds
are not separated by a decimal point.
Description: MySQL allows defining constants as DATE, TIME or TIMESTAMP ("DATETIME") data types: MySQL recognizes DATETIME and TIMESTAMP values in these formats: As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is permitted here, too: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '2012-12-31 11:30:45', '2012^12^31 11+30+45', '2012/12/31 11*30*45', and '2012@12@31 11^30^45' are equivalent. The date and time parts can be separated by T rather than a space. For example, '2012-12-31 11:30:45' '2012-12-31T11:30:45' are equivalent. As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'. As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'. http://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html The documentation does not reflect behavior related to support for fractional seconds, some of which is curious. 1. Using a format of "YYYYMMDDHHMMSS" works, as does supplying a fractional second value, so long as it does not exceed two digits ("YYYYMMDDHHMMSSFF"). Anything beyond two digit precision in fractional seconds causes MySQL to be unable to parse as a DATETIME/TIMESTAMP: mysql> SELECT TIMESTAMP '20130710010203'; +----------------------------+ | TIMESTAMP '20130710010203' | +----------------------------+ | 2013-07-10 01:02:03 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT TIMESTAMP '201307100102031'; +-----------------------------+ | TIMESTAMP '201307100102031' | +-----------------------------+ | 2013-07-10 01:02:03.1 | +-----------------------------+ 1 row in set (0.00 sec) mysql> SELECT TIMESTAMP '2013071001020312'; +------------------------------+ | TIMESTAMP '2013071001020312' | +------------------------------+ | 2013-07-10 01:02:03.12 | +------------------------------+ 1 row in set (0.00 sec) mysql> SELECT TIMESTAMP '20130710010203123'; ERROR 1525 (HY000): Incorrect DATETIME value: '20130710010203123' mysql> SELECT TIMESTAMP '201307100102031234'; ERROR 1525 (HY000): Incorrect DATETIME value: '201307100102031234' mysql> The ability to handle up to two digits of precision is curious, as one might expect support for up to six digits (maximum precision supported by MySQL 5.6 temporal data types), or none at all. 2. Contrary to the documentation statement that "any punctuation character may be used as the delimiter between date parts or time parts", the separator for the fractional seconds, when one is supplied, *must* be a period: mysql> SELECT TIMESTAMP '2013-07-10T01:02:03:1234'; ERROR 1525 (HY000): Incorrect DATETIME value: '2013-07-10T01:02:03:1234' mysql> SELECT TIMESTAMP '2013-07-10T01:02:03,1234'; ERROR 1525 (HY000): Incorrect DATETIME value: '2013-07-10T01:02:03,1234' mysql> SELECT TIMESTAMP '2013-07-10T01:02:03.1234'; +--------------------------------------+ | TIMESTAMP '2013-07-10T01:02:03.1234' | +--------------------------------------+ | 2013-07-10 01:02:03.1234 | +--------------------------------------+ 1 row in set (0.00 sec) Confusing matters, it is possible to use the period as the delimiter between other time components: mysql> SELECT TIMESTAMP '2013-07-10T01.02.03.1234'; +--------------------------------------+ | TIMESTAMP '2013-07-10T01.02.03.1234' | +--------------------------------------+ | 2013-07-10 01:02:03.1234 | +--------------------------------------+ 1 row in set (0.00 sec) This produces results which conflict with the behavior reported in BUG#69705 : mysql> SELECT TIMESTAMP '2013-07-10T01:02.03'; +---------------------------------+ | TIMESTAMP '2013-07-10T01:02.03' | +---------------------------------+ | 2013-07-10 01:02:03 | +---------------------------------+ 1 row in set (0.00 sec) How to repeat: SELECT TIMESTAMP '20130710010203'; SELECT TIMESTAMP '201307100102031'; SELECT TIMESTAMP '2013071001020312'; SELECT TIMESTAMP '20130710010203123'; SELECT TIMESTAMP '2013-07-10T01:02:03:1234'; SELECT TIMESTAMP '2013-07-10T01:02:03,1234'; SELECT TIMESTAMP '2013-07-10T01:02:03.1234'; SELECT TIMESTAMP '2013-07-10T01.02.03.1234'; Suggested fix: 1. Allow up to 6 digits of fractional second precision in parsing non-delimited string formats. 2. Restrict (and document) fractional second delimiter to period or comma only. 3. Restrict (and document) period and comma characters from being used as delimiters besides between second and fractional seconds.