为了更快的定位您的问题，请提供以下信息，谢谢
【详述】我们在做毫毛时间差对比时发现，表中通过 str_to_date转换后会丢失毫毛精度。
【背景】做过哪些操作？
【业务影响】
【是否存算分离】否
【StarRocks版本】3.3.14
【集群规模】例如：3fe（1 follower+2observer）+5be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【联系方式】社区一群-峻
mysql> select biz_time,create_time,milliseconds_diff(DATE_FORMAT(STR_TO_DATE(biz_time, ‘%Y-%m-%d %H:%i:%s.%f’), ‘%Y-%m-%d %H:%i:%s.%f’),DATE_FORMAT(STR_TO_DATE(create_time, ‘%Y-%m-%d %H:%i:%s.%f’), ‘%Y-%m-%d %H:%i:%s.%f’)) test from test_date2;
±------------------------±------------------------±-----+
| biz_time | create_time | test |
±------------------------±------------------------±-----+
| 2024-09-09 13:17:39.906 | 2024-09-09 13:17:39.702 | 0 |
±------------------------±------------------------±-----+
1 row in set (0.01 sec)
结果：精度丢失
mysql> select milliseconds_diff(DATE_FORMAT(STR_TO_DATE(‘2024-09-09 13:17:39.906’, ‘%Y-%m-%d %H:%i:%s.%f’), ‘%Y-%m-%d %H:%i:%s.%f’),DATE_FORMAT(STR_TO_DATE(‘2024-09-09 13:17:39.702’, ‘%Y-%m-%d %H:%i:%s.%f’), ‘%Y-%m-%d %H:%i:%s.%f’)) test;
±-----+
| test |
±-----+
| 204 |
±-----+
1 row in set (0.01 sec)
结果：正常
mysql> select * from test_date2;
±---------±------------------------±------------------------+
| order_id | biz_time | create_time |
±---------±------------------------±------------------------+
| 1 | 2024-09-09 13:17:39.906 | 2024-09-09 13:17:39.702 |
±---------±------------------------±------------------------+
1 row in set (0.01 sec)
mysql> show create table test_date2;
±-----------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±-----------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_date2 | CREATE TABLE test_date2 (
order_id bigint(20) NOT NULL COMMENT “”,
biz_time varchar(65533) NOT NULL COMMENT “”,
create_time varchar(65533) NOT NULL COMMENT “”
) ENGINE=OLAP
PRIMARY KEY(order_id)
DISTRIBUTED BY HASH(order_id)
PROPERTIES (
“compression” = “LZ4”,
“enable_persistent_index” = “true”,
“fast_schema_evolution” = “true”,
“replicated_storage” = “true”,
“replication_num” = “3”
); |
±-----------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)# s