| Bug #36466 | Adding days to day_microsecond changes interpretation of microseconds | ||
|---|---|---|---|
| Submitted: | 2 May 2008 9:57 | Modified: | 7 Mar 2010 18:45 |
| Reporter: | Janek Bogucki | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.0.45-Debian_1ubuntu3.3-log, 4.1, 5.0, 5.1, 6.0 BK | OS: | Linux |
| Assigned to: | Tatiana Azundris Nuernberg | CPU Architecture: | Any |
[2 May 2008 18:31]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php According to http://dev.mysql.com/doc/refman/5.0/en/datetime.html ("As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is allowed: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.") you can use almost any delimiter: mysql> select date_add('1000-01-01 00:00:00', interval '1.03:02:01.5' day_microsecond); +--------------------------------------------------------------------------+ | date_add('1000-01-01 00:00:00', interval '1.03:02:01.5' day_microsecond) | +--------------------------------------------------------------------------+ | 1000-01-02 03:02:01.500000 | +--------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('1000-01-01 00:00:00', interval '1#03:02:01.5' day_microsecond); +--------------------------------------------------------------------------+ | date_add('1000-01-01 00:00:00', interval '1#03:02:01.5' day_microsecond) | +--------------------------------------------------------------------------+ | 1000-01-02 03:02:01.500000 | +--------------------------------------------------------------------------+ 1 row in set (0.00 sec)
[2 May 2008 22:44]
Paul DuBois
No, there is a bug here. Consider this example in the original report:
mysql> select date_add('1000-01-01 00:00:00', interval '1.2' day_microsecond);
+-----------------------------------------------------------------+
| date_add('1000-01-01 00:00:00', interval '1.2' day_microsecond) |
+-----------------------------------------------------------------+
| 1000-01-01 00:00:01.000002 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
The ".2" part of the interval value is being treated like .000002, not .200000.
I expect the result of the query to be 1000-01-01 00:00:01.200000 rather than the value shown.
[6 May 2008 9:02]
Sveta Smirnova
Paul, thank you for the explanation. Got what I misread in original description. Verified as described.
[13 May 2008 6:48]
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/46642 ChangeSet@1.2624, 2008-05-13 08:47:36+02:00, tnurnberg@noir.wlan.koehntopp.de +3 -0 Bug #36466: Adding days to day_microsecond changes interpretation of microseconds When less than six places are given for microseconds, we zerofill from the right (leftmost place is always 1/10s). We only did this when all announced date/time fields were given; now we also format fractional seconds when more significant fields are left out.
[13 May 2008 6:51]
Tatiana Azundris Nuernberg
docs team, please remember that these are two bugs: - code bug: fractional seconds were not formatted/parsed correctly when more significant fields were implied/left out (patch attached) - docs bug: DAY_MICROSECOND, like the other INTERVAL-types, implies not just DAY and MICROSECOND, but everything in between. thanks, -T-
[8 Jan 2009 2:43]
Tatiana Azundris Nuernberg
queued for 6.0.10 in bugteam tree
[20 Jan 2009 18:54]
Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:mattias.jonsson@sun.com-20090108101840-vq8vzx31v17wzurz) (merge vers: 6.0.10-alpha) (pib:6)
[6 Feb 2009 20:31]
Paul DuBois
Noted in 6.0.10 changelog. When parsing or formatting interval values of DAY_MICROSECOND type, fractional seconds were not handled correctly when more-significant fields were implied or omitted. Also: Fixed expected format for DAY_MICROSECOND type in functions chapter.
[6 Feb 2009 20:36]
Paul DuBois
Also: Fixed expected format for MINUTE_MICROSEOND, HOUR_MICROSECOND, which were similarly inaccurate.
[3 Nov 2009 22:29]
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/89230 2905 Magne Mahre 2009-11-03 Bug #36466: Adding days to day_microsecond changes interpretation of microseco When less than six places are given for microseconds, we zerofill from the right (leftmost place is always 1/10s). We only did this when all announced date/time fields were given; now we also format fractional seconds when more significant fields are left out. @ mysql-test/r/func_time.result show that we treat fractions of seconds correctly (zerofill from right to six places) even if we left out fields on the left @ mysql-test/t/func_time.test show that we treat fractions of seconds correctly (zerofill from right to six places) even if we left out fields on the left @ sql/item_timefunc.cc format fractions of seconds even if announced more significant fields were left out
[3 Nov 2009 22:30]
Magne Mæhre
Backported to 5.5.0 (6.0-codebase revid: 2944.2.1)
[20 Nov 2009 12:55]
Bugs System
Pushed into 5.6.0-beta (revid:davi.arnaut@sun.com-20091119234808-xbjpkwaxjt5x5c0b) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:13)
[20 Nov 2009 12:58]
Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:13)
[22 Nov 2009 0:03]
Paul DuBois
Noted in 5.6.0 changelog. Already fixed in 6.0.x.
[6 Mar 2010 10:53]
Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:16)
[7 Mar 2010 18:45]
Paul DuBois
Moved 5.6.0 changelog entry to 5.5.3.
Description: Adding a day part to the day_microsecond expression changes the interpretation of the microseconds part from 5 microseconds to 500,000 microseconds mysql> select date_add('1000-01-01 00:00:00', interval '03:02:01.5' day_microsecond); +------------------------------------------------------------------------+ | date_add('1000-01-01 00:00:00', interval '03:02:01.5' day_microsecond) | +------------------------------------------------------------------------+ | 1000-01-01 03:02:01.000005 | +------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('1000-01-01 00:00:00', interval '1 03:02:01.5' day_microsecond); +--------------------------------------------------------------------------+ | date_add('1000-01-01 00:00:00', interval '1 03:02:01.5' day_microsecond) | +--------------------------------------------------------------------------+ | 1000-01-02 03:02:01.500000 | +--------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------------------- The documentation for day_microseconds appears to be wrong: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-add The format for the unit DAY_MICROSECOND is DAYS HOURS:MINUTES:SECONDS.MICROSECONDS not DAYS.MICROSECONDS as the documentation currently states. These statements demonstrate that mysql> select date_add('1000-01-01 00:00:00', interval '1.2' day_microsecond); +-----------------------------------------------------------------+ | date_add('1000-01-01 00:00:00', interval '1.2' day_microsecond) | +-----------------------------------------------------------------+ | 1000-01-01 00:00:01.000002 | +-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('1000-01-01 00:00:00', interval '1 2:3:4.5' day_microsecond); +-----------------------------------------------------------------------+ | date_add('1000-01-01 00:00:00', interval '1 2:3:4.5' day_microsecond) | +-----------------------------------------------------------------------+ | 1000-01-02 02:03:04.500000 | +-----------------------------------------------------------------------+ How to repeat: Enter these statements select date_add('1000-01-01 00:00:00', interval '03:02:01.5' day_microsecond); select date_add('1000-01-01 00:00:00', interval '1 03:02:01.5' day_microsecond);