今天研发在修复数据的时候发现代码的日志数据和mysql的数据对不上,于是开始了问题查找,发现是代码写入的时间字段是'2019-02-05 09:08:48.567',但数据库的时间字段为datetime,导致时间进位引起的,具体测试如下:
表结构:
mysql> desc test;+------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+--------------+------+-----+---------+----------------+| id | int | NO | PRI | NULL | auto_increment || date0 | date | YES | | NULL | || time0 | datetime | YES | | NULL | || time3 | datetime(3) | YES | | NULL | || timestamp0 | timestamp | YES | | NULL | || timestamp3 | timestamp(3) | YES | | NULL | |+------------+--------------+------+-----+---------+----------------+6 rows in set (0.00 sec)
插入数据:
mysql> insert into test (date0,time0,time3,timestamp0,timestamp3)values('2019-03-03 13:00:00','2019-03-03 13:00:00.500','2019-03-03 13:00:00.500500','2019-03-03 13:00:00.500','2019-03-03 13:00:00.500500');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> insert into test (date0,time0,time3,timestamp0,timestamp3)values('2019-03-03 13:00:00','2019-03-03 13:00:00.400','2019-03-03 13:00:00.500400','2019-03-03 13:00:00.400','2019-03-03 13:00:00.500400');Query OK, 1 row affected, 1 warning (0.00 sec)
查询数据:
mysql> select * from test;+----+------------+---------------------+-------------------------+---------------------+-------------------------+| id | date0 | time0 | time3 | timestamp0 | timestamp3 |+----+------------+---------------------+-------------------------+---------------------+-------------------------+| 1 | 2019-03-03 | 2019-03-03 13:00:01 | 2019-03-03 13:00:00.501 | 2019-03-03 13:00:01 | 2019-03-03 13:00:00.501 || 2 | 2019-03-03 | 2019-03-03 13:00:00 | 2019-03-03 13:00:00.500 | 2019-03-03 13:00:00 | 2019-03-03 13:00:00.500 |+----+------------+---------------------+-------------------------+---------------------+-------------------------+2 rows in set (0.00 sec)
由此可见:
当我们写入时间字段有毫秒值,但是数据库实际没有毫秒值的时候,数据库记录的时间会根据四舍五入原则执行进位或者截取,如果在时间精度严格的数据库上在写入类似'2019-03-03 23:59:59.600'这样的时间的话会导致数据统计到下一天,造成统计不一致问题。
解决方法:一定要统一数据时间字段的设置和写入的时间精度。
原文转载:http://www.shaoqun.com/a/619648.html
bonanza:https://www.ikjzd.com/w/275.html
电霸:https://www.ikjzd.com/w/2597
今天研发在修复数据的时候发现代码的日志数据和mysql的数据对不上,于是开始了问题查找,发现是代码写入的时间字段是'2019-02-0509:08:48.567',但数据库的时间字段为datetime,导致时间进位引起的,具体测试如下:表结构:mysql>desctest;+------------+--------------+------+-----+---------+--------
skyee:https://www.ikjzd.com/w/290
贸发局:https://www.ikjzd.com/w/1621
亚马逊t恤:https://www.ikjzd.com/w/1932
从选品到选品布局看卖家如何高密度的提升运营技能!:https://www.ikjzd.com/home/22776
被丢货、被盗窃!亚马逊上的中国卖家不高兴!:https://www.ikjzd.com/home/18607
简单易懂的快速筛选精准关键词法,亚马逊新手卖家却容易忽略:https://www.ikjzd.com/home/141622
No comments:
Post a Comment