| Bug #79548 | In "Update t1 set b(date) = c(datetime)", micro seconds are rounded | ||
|---|---|---|---|
| Submitted: | 7 Dec 2015 15:12 | Modified: | 9 Dec 2015 19:42 |
| Reporter: | Su Dylan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.7.8 | OS: | Any |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
[7 Dec 2015 18:13]
MySQL Verification Team
Thank you for your report, but this is not a bug. Simply, datetime, timestamp accept time in milliseconds. DATE accepts only year, month and date. What you have observed in your report is that a datetime constant is fed into date column. In those cases, according to our manual and SQL standard, a conversion from datetime to date takes place. Hence, rounding is absolutely necessary. Not a bug.
[8 Dec 2015 3:09]
Su Dylan
Hi Sinisa, As the document says, when datetime values are coverted to date, the time part is discarded, not rounded as you mentioned. Please help to double check. Thanks. Quote ==== Conversion of DATETIME and TIMESTAMP values: Conversion to a DATE value discards the time part because the DATE type contains no time information. ==== https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-conversion.html
[8 Dec 2015 15:13]
MySQL Verification Team
Thank you for your report. It turns out that you are right !!!!! Our manual is wrong, so I am verifying this as a documentation bug. I have discovered, together with our developers, that code works correctly, while our manual is wrong. Fully verified as a documentation bug.
[9 Dec 2015 19:42]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly. A change in behavior occurred in MySQL 5.6.4 with the introduction of fractional-seconds support: Before MySQL 5.6.4, conversion to a DATE value discards the time part because the DATE type contains no time information. As of MySQL 5.6.4, fractional seconds are taken into account and conversion to DATE rounds the time part. For example, '1999-12-31 23:59:59.499' becomes '1999-12-31', whereas '1999-12-31 23:59:59.500' becomes '2000-01-01'.
Description: Output: ======= mysql> set sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> mysql> drop table t1; create table t1(a datetime(3), b date); Query OK, 0 rows affected (0.00 sec) iQuery OK, 0 rows affected (0.01 sec) mysql> insert into t1(a) values ('9999-12-31 23:59:59.499'), ('9999-12-31 23:59:59.500'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> update t1 set b = a; Query OK, 2 rows affected, 3 warnings (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 3 mysql> select * from t1; +-------------------------+------------+ | a | b | +-------------------------+------------+ | 9999-12-31 23:59:59.499 | 9999-12-31 | | 9999-12-31 23:59:59.500 | 0000-00-00 | +-------------------------+------------+ 2 rows in set (0.00 sec) Problems: ========= After the update, '9999-12-31' is expected for column b for both rows. How to repeat: set sql_mode = ''; drop table t1; create table t1(a datetime(3), b date); insert into t1(a) values ('9999-12-31 23:59:59.499'), ('9999-12-31 23:59:59.500'); update t1 set b = a; select * from t1; Suggested fix: After the update, '9999-12-31' is expected for column b for both rows.