MySQL、MariaDB、TiDB时间小数秒四舍五入与截断

1. MySQL、MariaDB、TiDB 时间小数秒四舍五入与截断

当写入数据库时指定的时间小数秒超过数据库 DATETIME、TIMESTAMP 等字段精度时，不同数据库的处理不相同，如下所示：

数据库超过精度时间小数秒的处理MySQL 5.5截断MySQL 5.6 5.7 8.0.0四舍五入MySQL >= 8.0.1四舍五入 / 截断MariaDB截断TiDB四舍五入

2. 可能出现问题

在 Java 代码中将某个时间以 String、long 格式在不同系统间传递时，假如不同系统使用的数据库对超过精度时间小数秒的处理不同，当以上时间的小数部分大于等于 0.5 秒时，不同系统记录的同一个时间会相差 1 秒

对于进行四舍五入的数据库，以上时间是下一秒；对于进行截断的数据库，以上时间是当前秒

假如当前时间是某天、某月、某年的最后一秒，变成下一秒时，会变成明天、下个月、明年的时间，可能产生较大的影响

3. 可能出现问题的示例

假如在某系统的 Java 代码中将 Date 类型的时间写入 MySQL/TiDB 数据库精度为秒的 DATETIME、TIMESTAMP 等字段，则大约一半的时间为当前时间，一半的时间为下一秒

将以上 Date 类型的时间转换成精度为秒的字符串格式后，传递给其他系统，在其他系统中将时间写入 MariaDB 数据库精度为秒的 DATETIME、TIMESTAMP 等字段，则所有时间都是当前时间

以上不同系统中存储的同一个时间会出现不同的情况

4. 解决以上问题的方法

为了解决以上问题，在写入数据库中精度为秒的 DATETIME、TIMESTAMP 等字段时，需要在 Java 代码中将 Date 类型的时间毫秒清 0，如下所示：

Calendar calendar = Calendar.getInstance();

calendar.setTime(new Date());

calendar.set(Calendar.MILLISECOND, 0);

Date date = calendar.getTime();

这样无论数据库对超过精度时间小数秒进行哪种处理，写入数据库的时间都会是当前时间，不会是下一秒

5. Java 代码向数据库写入 Date 类型时间分析

Java 代码向数据库写入 Date 类型时间时进行抓包，分析 MySQL connector 向 MySQL 服务器发送的时间格式

直接使用 Date 的值

在 Java 代码中直接使用 Date 的值写入数据库时，向 MySQL 服务器发送的时间格式示例如下：

insert into table (time1) values ('2023-12-08 10:16:19.925')

写入的时间是字符串格式，存在小数秒

使用毫秒为 0 的 Date 值

在 Java 代码中将 Date 的毫秒清 0 后写入数据库时，向 MySQL 服务器发送的时间格式示例如下：

insert into table (time1) values ('2023-12-07 21:45:13')

写入的时间是字符串格式，精度为秒，没有小数秒

6. 相关文档

6.1. MySQL

6.1.1. MySQL 5.6、5.7

https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html

MySQL 5.6、5.7 版本的文档说明相同

MySQL has fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision:

To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision.

The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

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.

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.

MySQL 的 TIME, DATETIME, TIMESTAMP 类型的字段支持小数秒，小数秒的精度支持 0 到 6 位，默认为 0，即没有小数秒

在插入时间时假如超过字段的精度，会进行四舍五入

在进行四舍五入时不会有告警或异常出现，这个行为属于 SQL 标准，不会受服务器 sql_mode 设置的影响

6.1.2. MySQL 8.0

https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html

No warning or error is given when such rounding occurs. This behavior follows the SQL standard.

To insert the values with truncation instead, enable the TIME_TRUNCATE_FRACTIONAL SQL mode:

SET @@sql_mode = sys.list_add(@@sql_mode, 'TIME_TRUNCATE_FRACTIONAL');

在 sql_mode 设置中启用 TIME_TRUNCATE_FRACTIONAL 时，可以对超过精度的小数秒进行截断

6.1.3. MySQL 对小数秒开始支持的版本

https://downloads.mysql.com/docs/mysql-5.6-relnotes-en.pdf

Changes in MySQL 5.6.4 (2011-12-20, Milestone 7)

Fractional Seconds Handling

• Incompatible Change: MySQL now permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

MySQL 从 5.6.4 版本开始支持小数秒

6.1.4. MySQL 8.0 开始支持截断小数秒的版本

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html

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.

从 MySQL 8.0.1 版本开始，支持通过 sql_mode 设置 TIME_TRUNCATE_FRACTIONAL 来决定对小数秒进行四舍五入还是截断

6.2. MariaDB

https://mariadb.com/kb/en/microseconds-in-mariadb/

Generally, the precision can be specified for any TIME, DATETIME, or TIMESTAMP column, in parentheses, after the type name. The datetime precision specifies number of digits after the decimal dot and can be any integer number from 0 to 6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.

Note: When you convert a temporal value to a value with a smaller precision, it will be truncated, not rounded. This is done to guarantee that the date part is not changed. For example:

SELECT CAST('2009-12-31 23:59:59.998877' as DATETIME(3));

-> 2009-12-31 23:59:59.998

MariaDB 在插入时间时假如超过字段的精度，会进行截断，不会进行四舍五入

6.3. TiDB

https://docs.pingcap.com/zh/tidb/stable/data-type-date-and-time

TiDB 4、5、6、7 版本的说明都相同

使用 type_name(fsp) 可以定义精确到小数的列，其中 type_name 可以是 TIME、DATETIME 或 TIMESTAMP。例如：

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

fsp 范围是 0 到 6。

0 表示没有小数部分。如果省略了 fsp，默认为 0。

当插入包含小数部分的 TIME、DATETIME 或 TIMESTAMP 时，如果小数部分的位数过少或过多，可能需要进行四舍五入。

7. 验证数据库对时间小数秒四舍五入与截断

7.1. 插入数据库表

使用以下 sql 语句向数据库表时间字段插入数据并查询：

create table test_time(

id VARCHAR(50) NOT NULL,

t DATETIME NOT NULL,

PRIMARY KEY (id)

);

insert into test_time(id, t) value (uuid(), '2023-12-07 21:45:13.111');

insert into test_time(id, t) value (uuid(), '2023-12-07 21:45:13.666');

insert into test_time(id, t) value (uuid(), '2023-12-07 23:59:59.666');

insert into test_time(id, t) value (uuid(), '2023-11-30 23:59:59.666');

insert into test_time(id, t) value (uuid(), '2023-12-31 23:59:59.666');

select t from test_time;

使用 MySQL、TiDB 时，进行了四舍五入：

2023/12/7 21:45:13

2023/12/7 21:45:14

2023/12/8 0:00:00

2023/12/1 0:00:00

2024/1/1 0:00:00

使用 MariaDB 时，没有进行四舍五入：

2023-12-07 21:45:13

2023-12-07 23:59:59

2023-11-30 23:59:59

2023-12-31 23:59:59

7.2. 使用 CAST 直接查询

可以使用 CAST 直接查询

SELECT CAST('2023-12-07 21:45:13.111' as DATETIME);

SELECT CAST('2023-12-07 21:45:13.666' as DATETIME);

SELECT CAST('2023-12-07 23:59:59.666' as DATETIME);

SELECT CAST('2023-11-30 23:59:59.666' as DATETIME);

SELECT CAST('2023-12-31 23:59:59.666' as DATETIME);

精彩文章

评论可见，请评论后查看内容，谢谢！！！

您阅读本篇文章共花了：

金钥匙

MySQL、MariaDB、TiDB时间小数秒四舍五入与截断

android could not read ok from ADB Server

shell Android adb常用50条命令

发表评论取消回复

金钥匙

MySQL、MariaDB、TiDB时间小数秒四舍五入与截断

android could not read ok from ADB Server

shell Android adb常用50条命令

相关文章

发表评论取消回复