❤️mysql查询当前时间一个月以内的数据

小明的学习圈子2023-10-07数据库MySQL 日期

mysql 查询当前时间一个月以内的数据并按日期排序

SELECT
	* 
FROM
	(
	SELECT
		personname 
	FROM
		personnel_filing 
	WHERE
		operationType IN ( 'add', 'del' ) 
		AND operationTime BETWEEN DATE_SUB( NOW( ), INTERVAL 1 MONTH ) 
		AND NOW( ) 
	) AS temp 
ORDER BY
	operationTime DESC

DATE_SUB() 函数从日期减去指定的时间间隔,本文是从当前时间减去一个月。 CURDATE() 函数表示当前年月日,NOW()函数表示当前年月日时分秒,CURTIME()函数表示时分秒。

扩展

注意最好对修改成between and的形式,如果相减出现负数的时候可能会出问题。

查询昨天的数据

select * from test as 'time' where time.update_time <DATE_SUB(CURDATE(), INTERVAL 1 DAY)

查询当前这周的数据

select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 0 WEEK)

select * FROM test as 'time' where YEARWEEK(date_format(time.update_time,%Y-%m-%d’)) = YEARWEEK(now())

查询上一周的数据

select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 1 WEEK)

select * FROM test as 'time' WHERE YEARWEEK(date_format(time.update_time,%Y-%m-%d’)) = YEARWEEK(now())-1

查询上一个月的数据

select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

查询近两个月的数据(INTERVAL后面的数字改一下就可以了)

select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 2 MONTH)

查询上一年的数据

select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

查询距离当前现在6个月的数据

select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

查询今天的数据

select * from test as time where to_days(time.update_time ) =to_days(now());

查询本周的数据

select * FROM test as time WHERE YEARWEEK(date_format(time.update_time ,'%Y-%m-%d')) = YEARWEEK(now());

查询本月的数据

select * FROM test as time WHERE DATE_FORMAT(time.update_time , '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

查询本季度的数据

select * from test as time where QUARTER(time.update_time)=QUARTER(now());

查询本年的数据

select * from test as time where YEAR(time.update_time)=YEAR(NOW());

查询近一段时间数据(近一周、一个月、半年、一年)

//查询一周
格式:select * from 表名称 where DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(表内时间字段);
语句:select * from garbage_collect  where DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(collection_time);

//查询一个月
 格式:select * from 表名称 where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(表内时间字段);
 语句:select * from garbage_collect  where DATE_SUB(CURDATE(),  INTERVAL 1 MONTH) <= date(collection_time);

//查询近半年
格式:select * from 表名称 where DATE_SUB(CURDATE(),  INTERVAL 6 MONTH) <= date(表内时间字段);
语句:select * from garbage_collect  where DATE_SUB(CURDATE(),  INTERVAL 6 MONTH) <= date(collection_time);

查询指定时间段数据(开始时间-结束时间)–包含结束时间加DATE_ADD()函数:

格式:SELECT * FROM 表名称 WHERE 表字段 BETWEEN "开始时间" AND "结束时间";
语句:SELECT * FROM garbage_collect WHERE collection_time  BETWEEN "2020-02-01" AND "2020-03-31";

//查询指定时间段数据(开始时间-结束时间)包含结束时间
语句:SELECT * FROM garbage_collect WHERE  collection_time  BETWEEN "2020-03-17" AND DATE_ADD("2020-03-17",INTERVAL 1 DAY)

查询指定时间数据(年、月、日)

//查询2020年的数据:
格式:SELECT * FROM 表名称 WHERE year(表名称.表字段)="某年" 
语句:SELECT * FROM garbage_collect WHERE year(garbage_collect.collection_time)="2020" 
//查询9月份的数据:
格式:SELECT * FROM 表名称 WHERE year(表名称.表字段)="某月" 
语句:SELECT * FROM garbage_collect WHERE month(garbage_collect.collection_time)="09" 
//查询时期是08的数据:
格式:SELECT * FROM 表名称 WHERE year(表名称.表字段)="某日" 
语句:SELECT * FROM garbage_collect WHERE day(garbage_collect.collection_time)="08" 
//查询2020年03月份的数据:
SELECT * FROM garbage_collect WHERE year(garbage_collect.collection_time)="2020" and month(garbage_collect.collection_time)="03"
//查询2020年03月06日的数据:
SELECT * FROM garbage_collect WHERE year(garbage_collect.collection_time)="2020" and month(garbage_collect.collection_time)="03" AND DAY(garbage_collect.collection_time)="06";

Last Updated 2024/4/6 10:47:15