时间段筛选sql语句
选择某年-⽉-⽇的数据，lc_datetime数据类型为datetime
$t1=date("Y-m-d");
DATE_FORMAT(lc_datetime,'%Y-%m-%d')='{$t1}'
选择某1：年-⽉-⽇ - 某2：年-⽉-⽇之间的数据
– 查询指定时间段的数据
select fullName,addedTime FROM t_user where addedTime between  '2017-1-1 00:00:00'and'2018-1-1 00:00:00';
select fullName,addedTime FROM t_user where addedTime >='2017-1-1 00:00:00'and addedTime <'2018-1-1 00:00:00';
查询某个时间段的数据（半闭半开区间）
包含5，不包含8
SELECT check_type,create_time FROM attendance WHERE create_time between '2019-11-05'and'2019-11-08';
查询某个时间段的数据（闭区间）
包含5，也包含8
SELECT check_type,create_time FROM attendance WHERE date_format(create_time,'%Y-%m-%d') between '2019-11-05'and'2019-11-08';
其他⼀些语句
--今天
select fullName,addedTime from t_user where to_days(addedTime)<=to_days(now());
--昨天
select fullName,addedTime from t_user where to_days(NOW())-TO_DAYS(addedTime)<=1;
--近7天
select fullName,addedTime from t_user where date_sub(CURDATE(),INTERVAL7DAY)<=DATE(addedTime);
--近30天
SELECT fullName,addedTime FROM t_user where DATE_SUB(CURDATE(),INTERVAL30DAY)<=date(addedTime);
--本⽉
SELECT fullName,addedTime FROM t_user WHERE DATE_FORMAT( addedTime,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');
--上⼀⽉
SELECT fullName,addedTime FROM t_user WHERE PERIOD_DIFF(date_format(now(),'%Y%m'),date_format( addedTime,'%Y%m'))=1;
--查询本季度数据
select fullName,addedTime FROM t_user where QUARTER(addedTime)=QUARTER(now());
select语句查询日期
--查询上季度数据
select fullName,addedTime FROM t_user where QUARTER(addedTime)=QUARTER(DATE_SUB(now(),interval 1QUARTER));
--查询本年数据
select fullName,addedTime FROM t_user where YEAR(addedTime)=YEAR(NOW());
--查询上年数据
select fullName,addedTime FROM t_user where year(addedTime)=year(date_sub(now(),interval 1 year));
--查询距离当前现在6个⽉的数据
select fullName,addedTime FROM t_user where addedTime between date_sub(now(),interval 6 month)and now();
--查询当前这周的数据
SELECT fullName,addedTime FROM t_user WHERE YEARWEEK(date_format(addedTime,'%Y-%m-%d'))=YEARWEEK(now());
--查询上周的数据
SELECT fullName,addedTime FROM t_user WHERE YEARWEEK(date_format(addedTime,'%Y-%m-%d'))=YEARWEEK(now())-1;
--查询上个⽉的数据
select fullName,addedTime FROM t_user where date_format(addedTime,'%Y-%m')=date_format(DATE_SUB(curdate(),INTERVAL1MONTH),'%Y-%m'); --查询当前⽉份的数据
select fullName,addedTime FROM t_user where DATE_FORMAT(addedTime,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');
select fullName,addedTime FROM t_user where date_format(addedTime,'%Y-%m')=date_format(now(),'%Y-%m');
--查询指定时间段的数据
select fullName,addedTime FROM t_user where addedTime between  '2017-1-1 00:00:00'and'2018-1-1 00:00:00';
select fullName,addedTime FROM t_user where addedTime >='2017-1-1 00:00:00'and addedTime <'2018-1-1 00:00:00';

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。