datetime 小数精度，能否禁用四舍五入

🪐 TiDB 技术问题
1

【TiDB 使用环境】生产环境
【TiDB 版本】v7.1.5
【操作系统】centos 7.9
【遇到的问题：问题现象及影响】

部分情况下，时间精度不希望四舍五入，希望直接将多余的小数截断，在 mysql 8.0 中有特性支持
https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html

但是 tidb 和 mysql 5.7 的行为一致，会默认四舍五入，且没有找到修改的方法，或者有什么其他的函数，可以实现类似的效果

甚至我想到了，向下取整

mysql> select cast('2020-01-01 01:01:01.1115' as DATETIME(3));
+-------------------------------------------------+
| cast('2020-01-01 01:01:01.1115' as DATETIME(3)) |
+-------------------------------------------------+
| 2020-01-01 01:01:01.112                         |
+-------------------------------------------------+
1 row in set (0.01 sec)

mysql> select from_unixtime(floor(unix_timestamp('2020-01-01 01:01:01.1115')*1000)/1000);
+----------------------------------------------------------------------------+
| from_unixtime(floor(unix_timestamp('2020-01-01 01:01:01.1115')*1000)/1000) |
+----------------------------------------------------------------------------+
| 2020-01-01 01:01:01.1110                                                   |
+----------------------------------------------------------------------------+
2

select LEFT(DATE_FORMAT(‘2020-01-01 01:01:01.1115’, ‘%Y-%m-%d %H:%i:%s.%f’), 23) AS tunc_date ;

3

– 原始值：2020-01-01 01:01:01.1115，期望截断为 .111
SELECT FROM_UNIXTIME(
FLOOR(UNIX_TIMESTAMP(‘2020-01-01 01:01:01.1115’) * 1000) / 1000
) AS truncated_datetime;

– 结果：2020-01-01 01:01:01.111

4

用字符串函数截断到一定长度

5

mysql 8.0 提供的新 sql_mode TIME_TRUNCATE_FRACTIONAL 确实有用

6

但问题来了，为什么你写入的值和字段定义，精度有出入呢

1 个赞
8

时戳数据类型，应该有毫秒

9

SELECT CAST(SUBSTRING(CAST(‘2020-01-01 01:01:01.1115’ AS CHAR), 1, 23) AS DATETIME(3));

10

substr(xxx,1,23)

11

如果已知数据一直是定长的话，直接substr截断就好

12

此话题已在最后回复的 7 天后被自动关闭。不再允许新回复。