MySQL 查询时间段内数据(有坑)

前言

今天要从 MySQL 数据库取出指定时间段内数据,很简单的一个 SQL 语句,但是却出现了莫名其妙的问题:漏数据,甚至查不到数据。

比如取 3 月 10 号到 3 月 20 号的数据,没有问题,但是取 3 月 8 号到 3 月 20 号的数据却一条都取不出来,SQL 语句执行返回的结果为空。

SQL 语句如下:

第一条取到了该时间段内的全部数据,第二条没有取出任何数据。

按道理我时间范围扩大,就算没有取到新数据,小时间范围内的数据也应该被取到呀。有时候还会出现其他奇怪的问题,比如取 3 月 12 号到 3 月 6 号的数据竟然能够取出来,因为很明显这个时间范围不是前小后大(并没有使用 between 来指定时间范围)。

为了排查问题出在哪里,我将 SQL 语句单独拎出来,放到 phpMyAdmin 里执行,发现执行结果和在 C# 中一致,说明问题没有出在 C# 这端。

接着排查,发现了一个问题:数据库表中,存放数据插入时间的字段 “ time ” 类型为 varchar :

于是猜想:会不会是因为数据库无法把 varchar 类型的数据准确的转化成时间进行比较?

于是新建了一个表格来测试一下,字段 “ time ” 的数据类型设置为 datetime:

然后使用之前的 SQL 语句再次测试,发现问题解决了,之前各种各样的疑难杂症都消失了。

所以说,当我们建表的时候,用来标识时间的字段一定要设置成 datetime 类型,要么使用 current_timestamp ,要么手动插入符合 MySQL 时间类型的数据。只有这样,MySQL 才能准确的进行 “ 按照时间查询数据 ” 。

下面附上各种时间段的查询语句示例:

— 今天
select * from t_user where to_days(time) <= to_days(now());
— 昨天
select * from t_user where to_days(NOW()) – TO_DAYS(time) <= 1;
— 近7天
select * from t_user where date_sub(CURDATE(),INTERVAL 7 DAY) <= DATE(time);
— 近30天
SELECT * FROM t_user where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(time);
— 本月
SELECT * FROM t_user WHERE DATE_FORMAT( time, ‘%Y%m’ ) = DATE_FORMAT( CURDATE() , ‘%Y%m’ );
— 上一月
SELECT * FROM t_user WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m’ ) , date_format( time, ‘%Y%m’ ) ) =1;
— 查询本季度数据
select * FROM t_user where QUARTER(time)=QUARTER(now());
— 查询上季度数据
select * FROM t_user where QUARTER(time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
— 查询本年数据
select * FROM t_user where YEAR(time)=YEAR(NOW());
— 查询上年数据
select * FROM t_user where year(time)=year(date_sub(now(),interval 1 year));
— 查询距离当前现在6个月的数据
select * FROM t_user where time between date_sub(now(),interval 6 month) and now();
— 查询当前这周的数据
SELECT * FROM t_user WHERE YEARWEEK(date_format(time,’%Y-%m-%d’)) = YEARWEEK(now());
— 查询上周的数据
SELECT * FROM t_user WHERE YEARWEEK(date_format(time,’%Y-%m-%d’)) = YEARWEEK(now())-1;
— 查询上个月的数据
select * FROM t_user where date_format(time,’%Y-%m’)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),’%Y-%m’);
— 查询当前月份的数据
select * FROM t_user where DATE_FORMAT(time,’%Y%m’) = DATE_FORMAT(CURDATE(),’%Y%m’);
select * FROM t_user where date_format(time,’%Y-%m’)=date_format(now(),’%Y-%m’);
— 查询指定时间段的数据
select * FROM t_user where time between ‘2017-1-1 00:00:00’ and ‘2018-1-1 00:00:00′;
select * FROM t_user where time>=’2017-1-1 00:00:00’ and time< ‘2018-1-1 00:00:00’;

发表评论

Powered by WordPress | Theme Revised from Doo

苏ICP备18047621号

Copyright © 2017-2024 追光者博客