MySQL 5.7.39 关于时间精度

前情提要

当EndTime的数据类型为datetime
when the end_time’s dataType is datetime;

entity.EndTime = DateTime.MaxValue;
context.Set<T>().Add(entity);

当保存 ‘9999-12-31 23:59:59’ 这个值时,发生报错。
A crash has happended in the program when saving ‘9999-12-31 23:59:59’

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> MySqlConnector.MySqlException (0x80004005): Datetime function: datetime field overflow

原因是 DateTime.MaxValue 的时间为 9999-12-31 23:59:59.999999
The cause is the value of DateTime.MaxValue was ‘9999-12-31 23:59:59.999999’

我又存了一遍如下的样例:
And Then I test a case as follow:

entity.StartTime = Convert.ToDateTime("2023-07-30 09:00:00.599");
entity.EndTime = Convert.ToDateTime("2023-07-30 09:00:00.499");

结果如下:
The result as follow:

+---------------------+---------------------+
| start_time          | end_time            |
+---------------------+---------------------+
| 2023-07-30 09:00:01 | 2023-07-30 09:00:00 |
+---------------------+---------------------+

MySQL 5.7文档
A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is ‘YYYY-MM-DD hh:mm:ss[.fraction]’, the range for DATETIME values is ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.499999’, and the range for TIMESTAMP values is ‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.499999’. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see Section 11.2.7, “Fractional Seconds in Time Values”.

谷歌机翻：

DATETIME 或 TIMESTAMP 值可以包含精度高达微秒（6 位数字）的尾随小数秒部分。 特别是，插入 DATETIME 或 TIMESTAMP 列的值中的任何小数部分都会被存储而不是被丢弃。 包含小数部分后，这些值的格式为“YYYY-MM-DD hh:mm:ss[.fraction]”，DATETIME 值的范围为“1000-01-01 00:00:00.000000”到“9999” -12-31 23:59:59.499999’，TIMESTAMP 值的范围是 ‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.499999’。 小数部分应始终与其余时间用小数点分隔； 无法识别其他小数秒分隔符。 有关 MySQL 中秒小数部分支持的信息，请参阅第 11.2.7 节，“时间值中的小数秒”。

关于小数秒的解释
Inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding. Consider a table created and populated as follows:

CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
INSERT INTO fractest VALUES
('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');

The temporal values are inserted into the table with rounding:

mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1          | c2                     | c3                     |
+-------------+------------------------+------------------------+
| 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 |
+-------------+------------------------+------------------------+

No warning or error is given when such rounding occurs. This behavior follows the SQL standard, and is not affected by the server sql_mode setting.

谷歌机翻：

将带有秒小数部分的 TIME、DATE 或 TIMESTAMP 值插入到相同类型但小数位数较少的列中会导致四舍五入。 考虑按如下方式创建和填充的表：

CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
INSERT INTO fractest VALUES
('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');

时间值通过舍入插入到表中：

mysql> SELECT * FROM fractest；
+-------------+------------------------+---------- --------------+
| c1 | c2 | c3 |
+-------------+------------------------+---------- --------------+
| 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 |
+-------------+------------------------+---------- --------------+

发生此类舍入时，不会给出警告或错误。 此行为遵循 SQL 标准，并且不受服务器 sql_mode 设置的影响。