| Bug #38435 | LONG Microseconds cause MySQL to fail a CAST to DATETIME or DATE | ||
|---|---|---|---|
| Submitted: | 29 Jul 2008 19:36 | Modified: | 30 Jul 2008 9:43 |
| Reporter: | Emery Fabrice NZEYIMANA | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S4 (Feature request) |
| Version: | 5.0.45 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | cast, date, datetime, MICROSECOND | ||
[29 Jul 2008 20:24]
Sveta Smirnova
Thank you for the reasonable feature request.
[30 Jul 2008 9:43]
Emery Fabrice NZEYIMANA
Hello, Is this really a feature request? Or it's a bug that needs to be fixed? If we can get 10 from a string like '10abcd' (an unlikely number), I think we should be able to get 2008-07-29 10:42:51 or 2008-07-29 10:42:51.355353 from '2008-07-29T10:42:51.3553533+02:00' (a valid representation of a datetime object). Why can't the server at least chop off the milliseconds?
[28 Jan 2009 9:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/64236 2751 Tatiana A. Nurnberg 2009-01-28 Bug#42146 - DATETIME fractional seconds parse error Bug#38435 - LONG Microseconds cause MySQL to fail a CAST to DATETIME or DATE Parsing of optional microsecond part in datetime did not fail gracefully when field width was larger than the allowed six places. Now handles up to the correct six places, and disregards any extra digits without messing up what we've already got.
[11 Feb 2009 15:52]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/65926 2794 Tatiana A. Nurnberg 2009-02-11 Bug#42146 - DATETIME fractional seconds parse error Bug#38435 - LONG Microseconds cause MySQL to fail a CAST to DATETIME or DATE Parsing of optional microsecond part in datetime did not fail gracefully when field width was larger than the allowed six places. Now handles up to the correct six places, and disregards any extra digits without messing up what we've already got.
[13 Feb 2009 7:13]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/66149 2794 Tatiana A. Nurnberg 2009-02-13 Bug#42146 - DATETIME fractional seconds parse error Bug#38435 - LONG Microseconds cause MySQL to fail a CAST to DATETIME or DATE Parsing of optional microsecond part in datetime did not fail gracefully when field width was larger than the allowed six places. Now handles up to the correct six places, and disregards any extra digits without messing up what we've already got.
[13 Feb 2009 18:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/66282 2794 Tatiana A. Nurnberg 2009-02-13 Bug#42146 - DATETIME fractional seconds parse error Bug#38435 - LONG Microseconds cause MySQL to fail a CAST to DATETIME or DATE Parsing of optional microsecond part in datetime did not fail gracefully when field width was larger than the allowed six places. Now handles up to the correct six places, and disregards any extra digits without messing up what we've already got.
[19 Feb 2009 13:03]
Bugs System
Pushed into 6.0.10-alpha (revid:sergey.glukhov@sun.com-20090218125737-5y5b2xo3duo1wlvo) (version source revid:azundris@mysql.com-20090213181016-5p7fvpozygkoplo9) (merge vers: 6.0.10-alpha) (pib:6)
[13 Mar 2009 19:04]
Bugs System
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:vvaintroub@mysql.com-20090218093153-sjzxf01i4ezte0xp) (merge vers: 5.1.33) (pib:6)
[9 May 2009 16:42]
Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:39]
Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:37]
Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)
Description: When a given datetime value contains long Microseconds (like 2008-07-29T10:42:51.3553533+02:00) {with the microseconds part longer than 6 digits}, MySQL fails to make the value a datetime or date. SELECT CAST('2008-07-29T10:42:51.3553533+02:00' AS DateTime) -----> This gives NULL But SELECT CAST('2008-07-29T10:42:51.355353+02:00' AS DateTime) -----> This gives 2008-07-29 10:42:51.355353 as expected It's true the manual says that the Microseconds part should be nnnnnn (6 digits) but I view this a limitation. I encountered this when using a C# built application (connecting as a webservice client). How to repeat: mysql> SELECT CAST('2008-07-29T10:42:51.3553533+02:00' AS DateTime) AS `TestDateTime` ; +--------------+ | TestDateTime | +--------------+ | NULL | +--------------+ 1 row in set, 1 warning (0.00 sec) CREATE TABLE `test_table` ( `somedate` DATETIME NOT NULL ); INSERT INTO `test_table` VALUES('2008-07-29T10:42:51.3553533+02:00'); mysql> SELECT `somedate` FROM `test_table`; +---------------------+ | somedate | +---------------------+ | 0000-00-00 00:00:00 | +---------------------+ 1 row in set (0.00 sec) Suggested fix: Change DateTime Parsing and take into considerations longer microseconds.