MySQL Bugs: #68760: Datetime rounding problem

Bug #68760	Datetime rounding problem
Submitted:	24 Mar 2013 21:08	Modified:	17 Nov 2016 17:40
Reporter:	ymhr ymhr	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.6.10/5.7	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Assigning a datetime value with decimal precision to a datetime field, the server will try to round it.
This is in contrast with the server version 5.5, where the decimal values are simply ignored.
To make things worse, assigning the common NET framework value DateTime.MaxValue will throw exception:
Error Code: 1441
Datetime function: datetime field overflow

I know that in 5.6 version the datetime can have decimal precision, but the default datetime field (datetime(0)) will be without decimal precision.

How to repeat:
CREATE TABLE test(  
  MOMENT DATETIME
);
INSERT INTO test VALUES ('2013-03-21 10:00:00.1');
INSERT INTO test VALUES ('2013-03-21 10:00:00.5');
INSERT INTO test VALUES ('2013-03-21 10:00:00.9');
INSERT INTO test VALUES ('9999-12-31 23:59:59.9'); -> throw exception in 5.6
select * from test;

SERVER 5.5 result
2013-03-21 10:00:00
2013-03-21 10:00:00
2013-03-21 10:00:00
9999-12-31 23:59:59

SERVER 5.6 result
2013-03-21 10:00:00
2013-03-21 10:00:01
2013-03-21 10:00:01

This is either a documentation request or an actual but, will let devs decide.
I guess the problem can be seen with a simpler test:

mysql> select cast('9999-12-31 23:59:59.9' as datetime),version()
+-------------------------------------------+-----------+
| cast('9999-12-31 23:59:59.9' as datetime) | version() |
+-------------------------------------------+-----------+
| NULL                                      | 5.6.10    |
+-------------------------------------------+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1441 | Datetime function: datetime field overflow |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

------------

mysql> select cast('9999-12-31 23:59:59.9' as datetime),version();
+-------------------------------------------+-----------+
| cast('9999-12-31 23:59:59.9' as datetime) | version() |
+-------------------------------------------+-----------+
| 9999-12-31 23:59:59.900000                | 5.5.30    |
+-------------------------------------------+-----------+
1 row in set (0.00 sec)

any news?

We now have the same issue and using the .NET connector this means we will have to review our entire code library for all instances of DateTime.MaxValue (C#). any news on a resolution?

I would propose that a session variable or schema switch be introduced that affects the rounding behavior of timestamps.

We have all of the xxxxx2 types now for the FSP types, so this should be extended to have a truncate method.  Truncate would emulate the behavior of pre-5.6 servers while the existing behavior would be the default.

We could either extend the schema language to "ROUND TRUNCATE" or "ROUND DEFAULT" for date/time types or we could add a per-session/global variable called "DATETIME_ROUNDING" and have the values of TRUNCATE or DEFAULT.

The core problem is that frameworks such as Hibernate do optimistic locking with fractional values and just happen to work correctly with truncate, but fail with rounding.  But this problem affects many legacy applications and frameworks that are retargeted to 5.6, so 5.6 and above should have options to behave like previous versions for compatibility.

http://bugs.mysql.com/bug.php?id=76948 marked as duplicate of this one.

Bug #78802 marked as duplicate of this

I also will need to search my code library for usages of DateTime.MaxValue (C#). It has been over 2 years with not update, so is this just ignored?

http://bugs.mysql.com/bug.php?id=82325 marked as duplicate of this one.

Posted by developer:
 
Noted in 8.0.1 changelog.

Inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds
part into a column having the same type but fewer fractional digits
resulted in rounding. This differs from MySQL 5.5, which used
truncation rather than rounding. To enable control over this
behavior, a new TIME_TRUNCATE_FRACTIONAL SQL mode is available. The
default is to use rounding. If this mode is enabled, truncation
occurs instead.

I am using MySql version 5.7.mysql_aurora.2.10.0

A Date value (2022-12-20 17:32:36.786') I am inserting in a table and updating in a different table. Both table column's data type is DATETIME.

I am seeing a difference of one sec.
Ex : In one table the value is 2022-12-20 17:32:37 and in other 2022-12-20 17:32:36

This problem report was closed a long time ago, so don't expect any further work on it. If you suspect a recent problem in MySQL, please file a new report with a reproducible test case and details about the MySQL version in use.