时间范围查询数据不准确案例

已于 2024-08-22 11:22:16 修改
阅读量633 收藏 16
点赞数 13
文章标签： mysql
于 2024-08-22 11:17:08 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/znfsw2008/article/details/141423411
版权

MySQL 时间范围查询数据不准确案例

问题

业务小伙伴遇到时间范围查询数据不准确的问题。具体如下：

请教个问题
expiry_Date 是date类型

select count(1) from tablename where expiry_Date >= '2024-08-20T00:00'
and expiry_Date <= '2024-08-20T23:59:59.999999999';

这个sql查询 会把 2024-08-21号的数据也查出来 

是因为我们没设置时区的问题?

咋一看,确实诡异啊！
明明只查1天的count(1)，你却给我2天的，作孽啊。。救救我~

排查

将查询条件中=去掉,改成 expiry_Date < '2024-08-20T23:59:59.999999999' 数据就是准确的。好像和日期精度有关。
从表结构开始排查~

#mysql5.7
CREATE TABLE `tablename` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  ......
  `expiry_Date` date DEFAULT NULL COMMENT '到期日',
  PRIMARY KEY (`id`),
  KEY `idx_expire_date` (`expiry_Date`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='xxx'

结论

从表结构发现：时间字段类型和传入时间格式不一致。

expiry_Date 字段是 Date 类型的，该类型标准格式是 YYYY-MM-DD。

查某天的，精确到天就可以了,比如
select count(1) from table_name where expiry_Date = '2024-08-20' ；

所以，该问题本质是传入了 DateTime 类型数据与Date 类型数据做比较，而传入数据 ‘2024-08-20T23:59:59.999999999’ 是精确到秒的小数后 9 位的，做时间范围比较时，MySQL 是将时间值四舍五入后作比较的。与时区无关。

可参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html 。

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

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.

测试复现：datetime 与 date 类型作比较

#mysql5.7
#datetime 类型与 date 类型做比较
mysql> desc zlz_date;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| col1  | date      | YES  |     | NULL              |                             |
| col2  | datetime  | YES  |     | NULL              |                             |
| col3  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)

mysql> select * from zlz_date;
+------------+---------------------+---------------------+
| col1       | col2                | col3                |
+------------+---------------------+---------------------+
| 2024-08-21 | 2024-08-21 00:00:00 | 2024-08-21 00:00:00 |
| 2024-08-20 | 2024-08-20 00:00:01 | 2024-08-20 00:00:01 |
| 2024-08-20 | 2024-08-20 23:59:59 | 2024-08-20 23:59:59 |
+------------+---------------------+---------------------+
3 rows in set (0.00 sec)

# datetime 数据精确到秒的小数后6位
mysql> select * from zlz_date where col1>='2024-08-20 00:00:00' and col1 <='2024-08-20 23:59:59.999999';
+------------+---------------------+---------------------+
| col1       | col2                | col3                |
+------------+---------------------+---------------------+
| 2024-08-20 | 2024-08-20 00:00:01 | 2024-08-20 00:00:01 |
| 2024-08-20 | 2024-08-20 23:59:59 | 2024-08-20 23:59:59 |
+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

# datetime 数据精确到秒的小数后9位
mysql> select * from zlz_date where col1>='2024-08-20 00:00:00' and col1 <='2024-08-20 23:59:59.999999999';
+------------+---------------------+---------------------+
| col1       | col2                | col3                |
+------------+---------------------+---------------------+
| 2024-08-21 | 2024-08-21 00:00:00 | 2024-08-21 00:00:00 |
| 2024-08-20 | 2024-08-20 00:00:01 | 2024-08-20 00:00:01 |
| 2024-08-20 | 2024-08-20 23:59:59 | 2024-08-20 23:59:59 |
+------------+---------------------+---------------------+
3 rows in set (0.00 sec)

总结

1、时间类型比较避坑指南

  1. 日期比较大小时，需要保证比较的两个日期都是同一类型的，否则会出现错误。
  2. 日期比较大小时，需要注意日期格式的一致性，否则会出现比较结果不准确的情况。
  3. 日期比较大小时，需要考虑时区的影响，尤其是在跨时区的应用中。
  4. MySQL 5.6 后，TIME, DATETIME, TIMESTAMP值提供小数秒支持，精度高达秒的小数后6位。
  5. 将带有秒小数部分的时间类型做比较时，小数位数较少的列中会导致四舍五入。

2、MySQL 时间类型怎么选

有关 date、datetime、time、year、timestamp 的支持范围和格式就不在老生常谈了。详见：https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-syntax.html 。

直接说结论：

  1. datetime类型格式是 YYYY-MM-DD HH:MM:SS，需要精确到时分秒的数据，建议直接上 datetime 类型。
  2. 从 MySQL 5.6 版本开始， TIMESTAMP 类型也能支持毫秒。与 DATETIME 不同的是，若带有毫秒时， TIMESTAMP 类型占用 7 个字节，而 DATETIME 无论是否存储毫秒信息，都占用 8 个字节。
  3. 目前的趋势下，建议所有的新表上线，可以直接用 datetime 替代 timestamp 类型。涉及到跨时区的问题，放到应用层来做。

时间字段设置建表举个栗子：

CREATE TABLE `student_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
 ......
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表'

3、DATA/DATATIME 类型可以做比较吗

可以。

  1. 当传入DATA类型时间与DATATIME类型时间进行比较的时候，DATA类型默认转化成更长的DATATIME类型时间，比如我传入一个DATA型时间为：2018/07/03，在与DATATIME类型时间比较的时候，应该变成：2018/07/03 00:00:00，然后再去比较。
  2. 当传入DATATIME类型时间与较短的DATA类型时间进行比较的时候，若带时间值带有秒后面的小数位，带有小数位的列会做四舍五入后再比较。
测试 date 与 datetime 类型做比较
mysql> desc zlz_date;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| col1  | date      | YES  |     | NULL              |                             |
| col2  | datetime  | YES  |     | NULL              |                             |
| col3  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)

mysql> select * from zlz_date;
+------------+---------------------+---------------------+
| col1       | col2                | col3                |
+------------+---------------------+---------------------+
| 2024-08-21 | 2024-08-21 00:00:00 | 2024-08-21 00:00:00 |
| 2024-08-20 | 2024-08-20 00:00:01 | 2024-08-20 00:00:01 |
| 2024-08-20 | 2024-08-20 23:59:59 | 2024-08-20 23:59:59 |
| 2024-08-21 | 2024-08-21 00:00:01 | 2024-08-21 00:00:01 |
+------------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from zlz_date where col2<='2024-08-21';
+------------+---------------------+---------------------+
| col1       | col2                | col3                |
+------------+---------------------+---------------------+
| 2024-08-21 | 2024-08-21 00:00:00 | 2024-08-21 00:00:00 |
| 2024-08-20 | 2024-08-20 00:00:01 | 2024-08-20 00:00:01 |
| 2024-08-20 | 2024-08-20 23:59:59 | 2024-08-20 23:59:59 |
+------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from zlz_date where col2>'2024-08-21';
+------------+---------------------+---------------------+
| col1       | col2                | col3                |
+------------+---------------------+---------------------+
| 2024-08-21 | 2024-08-21 00:00:01 | 2024-08-21 00:00:01 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

参考文档

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

PS：转载请注明出处，谢谢。

雨落竹林
关注
数据分析案例-某公司员工数据信息可视化
m0_64336780的博客
10-26 1万+
本次实验旨在通过可视化工具和技术，分析和呈现公司员工数据信息。这种数据可视化对于公司管理和决策制定非常重要，可以帮助领导层更好地了解员工情况，优化流程，提高效率，和制定人力资源策略。
mysql 日期区间查询问题
qq122453418的博客
01-19 1万+
在做mysql区间查询的时候，查询出的数据缺少最大时间的数据例如：表中有一个字段datetime类型的字段date,sql语句如下：select * from test where date between '2018-01-17' and '2018-01-18';select * from test where date >= '2018-01-17' and date 两个语句都得不到 da
MySQL数据库中时间类型的处理
aq_money的博客
03-30 1201
MySQL数据库中时间类型的处理 mysql 数据库中时间字段的说明 (1 date 字段 时间格式为 2022-3-27 (2 datetime 字段 时间格式为 2022-03-27 14:46:25 mysql数据库中 函数 使用方式 (1 YEAR() —可以取得日期年份 （2 select CURDATE() --返回系统当前时间 格式是 2022-3-27 （3 select SYSDATE() --返回系统当前时间 格式是 2022-03-27 14:46:25 mysql数据库.
oracle查询当前系统时间不准确可能情况
努力工作 认真玩
08-18 1989
时间日期格式 mm：月份 mi：分钟 错误情况 select to_char(sysdate,'yyyy-mm-dd,hh24:mm:ss') from dual; 正常情况 select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual; 参考资料 【CSDN】查询时间异常@https://blog.csdn.net/iteye_13498/article/details/82327095 【CSDN】oracle时间日期格式@https://blo
有趣的数据分析案例
hualalalalali的博客
08-16 4340
数据分析在我们的日常生活中起到的作用越来越重要，应用的场景也越来越多，在各个行业，都有数据分析的身影，数据分析的应用，提高了行业内的竞争力，同时对于消费者而言也是有利的。商家的活动针对性更强，同时节约了成本。下面，我将通过几个案例，举例在各个行业中数据分析的应用，感受数据分析背后的价值。 数据分析案例 1.医疗行业 在加拿大多伦多的一家医院，针对早产婴儿，每秒钟有超过3000次的数据读取。通...
案例上手 Python 数据可视化
热门推荐
GitChat
02-27 3万+
课程亮点 6 个常用的数据可视化工具使用方法 20 个案例贯穿各个工具的学习过程 基于 GUI / HTML / Web 网站多种绘图风格 中、美、日三国不同思想的制图工具 从静态到交互，满足不同需要的图示 课程背景 数据可视化是数据分析和机器学习的重要环节，比如数据清洗、特征工程、机器学习、数据分析（特别是报告）、评估等环节都会用到“数据可视化”技术。 数据可视化同时还广泛存在于各...
大数据SQL优化之数据倾斜解决案例全集
过往记忆大数据
01-18 5283
1 什么是数据倾斜数据倾斜即指在大数据计算任务中某个处理任务的进程（通常是一个JVM进程）被分配到的任务量过多，导致任务运行时间超长甚至最终失败，进而导致整个大任务超长时间运行或者失败。外...
Python数据分析案例28——西雅图交通事故预测（不平衡样本处理）
weixin_46277779的博客
05-24 5868
python机器学习案例，数据处理探索分析，不平衡样本采样
数分面试：如何保证数据准确性？
SeizeeveryDay的博客
08-11 2793
如何用科学的方法，保障数据准确性|0x00 问题描述我是小z前几天面试问了一个问题：怎么保证数据的正确性?以下是原文：上游，会遇到根源性问题，比如客户端在数据上报时就传错的情况，比如手抖把...
第86讲：MySQLDump与Binlog日志实现企业级数据备份恢复案例
江晓龙的博客
01-08 1万+
这个参数，此参数会在备份文件中帮我们记录：从备份开始的时间算，Binlog中最近的一个GTID号、当前使用的Binlog日志名称、Binlog中事件的最近一个Position标识位号，有了这三个信息之后，找起点不再是难事，我们可以非常快速的指定我们要截取那些Binlog日志。（本次模拟过程中，没有将数据库所有文件删除，是因为Binlog也在这个路径，如果模拟数据库文件全部损坏，那么修复的时候就需要重新初始化数据库的，Binlog也会被覆盖，因此这里只模拟平台库数据库文件损坏，全部丢失。
使用Excel数据分析案例数据.zip
01-08
例如，可以限制输入范围、要求输入特定格式（如日期、时间）等。 8. 公式审计：Excel的追踪引用、查看公式路径和错误检查工具，有助于理解公式工作原理和查找错误。 9. 宏和VBA编程：对于高级用户，Excel的宏和...
Python大数据处理案例
01-27
在这个Python大数据处理案例中，我们关注的是如何使用Python来分析共享自行车租赁数据，特别是与天气、时间等因素的关系。数据来源于Kaggle上的“华盛顿自行车共享需求”竞赛，其中包括两个文件，分别对应2011-2012...
某招聘网站数据分析案例及数据集.zip
11-07
在招聘网站的数据中，可能会遇到诸如职位描述不完整、薪资信息缺失或者求职者个人信息错误等问题，这些都需要通过数据清洗来修正，以确保后续分析的准确性。 接着，图表显示是将复杂数据转化为易于理解的视觉形式的...
时间序列示例的数据集.rar
06-08
在这个案例中，我们关注的是一个名为“时间序列示例的数据集.rar”的压缩包文件，其中包含了一个或多个时间序列数据文件，这些文件通常用于教学、研究或实际应用中的预测模型构建。 首先，我们要理解时间序列数据的...
基于SpringBoot+Vue+MySQL的养老院管理系统
程序员大金的博客
09-20 832
基于SpringBoot+Vue+MySQL的养老院管理系统，附源码。
Mysql梳理7——分页查询
haozihua的博客
09-22 430
注意：MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;如果我们知道返回结果只有 1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？背景2：表里有 4 条数据，我们只想要显示第 2、3 条数据怎么办呢？所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。第二个参数“行数”指示返回的记录条数。
基于Node.js+Express+MySQL+VUE实现的计算机毕业设计旅游推荐网站
最新发布
计算机程序开发设计
09-26 466
越来越多的人开始注重旅游的品质和体验，追求个性化、多样化的旅游方式。总之，本次计算机毕业设计项目旨在开发一款功能完善、用户友好的旅游推荐网站，通过先进的技术手段和创新的设计理念来满足广大旅游爱好者的个性化、高质量旅游需求。因此，本次计算机毕业设计项目选择开发一款旅游推荐网站，旨在通过先进的技术手段和创新的设计理念，打造一个更加符合用户需求、更加智能化的旅游信息平台。实现个性化的旅游推荐：通过分析用户的浏览历史、搜索记录和行为习惯，网站将能够为用户推荐符合其兴趣和需求的旅游产品和目的地。
Linux递归找出目录下最近被修改文件（最近一段时间内被修改过的最新文件）（最近修改文件、最新文件、查找文件）（监控目录、监控mysql文件）
Dontla的博客
09-26 432
目录下最近60分钟内修改的最新文件。如果你需要查找的时间范围不同，可以调整。表示最近10分钟）。
Oracle数据仓库案例：PROBE脏数据处理策略
脏数据是指那些不准确、不完整、不一致或者与预定义规则不符的数据，它们可能来源于源系统的错误、数据传输过程中的丢失或篡改，或者是随着时间推移而产生的数据质量下降。Oracle数据仓库解决方案中的PROBE工具，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值