Mysql日期查询的详细介绍

2023-07-21 17:11:04 158

查询当前日期

SELECTCURRENT_DATE();
SELECTCURDATE();

查询当前日期和时间

SELECTNOW();

查询今天的数据

SELECT*FROM`表名`WHERETO_DAYS(NOW())=TO_DAYS(`字段`);
SELECT*FROM`表名`WHERETO_DAYS(NOW())-TO_DAYS(`字段`)=0;

查询昨天的数据

SELECT*FROM``表名``WHERETO_DAYS(`字段`)=TO_DAYS(NOW())-1;
SELECT*FROM``表名``WHERETO_DAYS(`字段`)-TO_DAYS(NOW())=-1;
SELECT*FROM``表名``WHERETO_DAYS(NOW())=TO_DAYS(`字段`)+1;
SELECT*FROM``表名``WHERETO_DAYS(NOW())-TO_DAYS(`字段`)=1;

查询最近七天的数据

SELECT*FROM`表名`WHEREDATE_SUB(CURDATE(),INTERVAL7DAY)<=DATE(`字段`);

查询最近三十天的数据

SELECT*FROMtableWHEREDATE_SUB(CURDATE(),INTERVAL30DAY)<=DATE(`字段`);

查询本周的数据

SELECT*FROM`表名`WHEREYEARWEEK(date_format(`字段`,'%Y-%m-%d'))=YEARWEEK(CURDATE());
SELECT*FROM`表名`WHEREYEARWEEK(date_format(`字段`,'%Y-%m-%d'))-YEARWEEK(CURDATE())=0;
SELECT*FROM`表名`WHEREYEARWEEK(DATE_FORMAT(`字段`,'%Y-%m-%d'))=YEARWEEK(DATE_FORMAT(CURDATE(),'%Y-%m-%d'));
SELECT*FROM`表名`WHEREYEARWEEK(DATE_FORMAT(`字段`,'%Y-%m-%d'))-YEARWEEK(DATE_FORMAT(CURDATE(),'%Y-%m-%d'))=0;

查询上周的数据

SELECT*FROM`表名`WHEREYEARWEEK(DATE_FORMAT(`字段`,'%Y-%m-%d'))=YEARWEEK(DATE_FORMAT(CURDATE(),'%Y-%m-%d'))-1;
SELECT*FROM`表名`WHEREYEARWEEK(DATE_FORMAT(`字段`,'%Y-%m-%d'))-YEARWEEK(DATE_FORMAT(CURDATE(),'%Y-%m-%d'))=-1;
SELECT*FROM`表名`WHEREYEARWEEK(DATE_FORMAT(`字段`,'%Y-%m-%d'))=YEARWEEK(CURDATE())-1;
SELECT*FROM`表名`WHEREYEARWEEK(DATE_FORMAT(`字段`,'%Y-%m-%d'))-YEARWEEK(CURDATE())=-1;

查询当月的数据

SELECT*FROM`表名`WHEREDATE_FORMAT(`字段`,'%Y-%m')=DATE_FORMAT(CURDATE(),'%Y-%m');
SELECT*FROM`表名`WHEREMONTH(`字段`)-MONTH(NOW())=0;
SELECT*FROM`表名`WHEREMONTH(`字段`)=MONTH(NOW());

查询上月的数据

SELECT*FROM`表名`WHEREDATE_FORMAT(`字段`,'%Y-%m')=DATE_FORMAT(DATE_SUB(NOW(),INTERVAL1MONTH),'%Y-%m');

查询本季度的数据

SELECT*FROM`表名`WHEREQUARTER(`字段`)=QUARTER(NOW())ANDYEAR(`字段`)=YEAR(NOW());

查询上季度的数据

SELECT*FROM`表名`WHEREQUARTER(`字段`)=QUARTER(DATE_SUB(NOW(),INTERVAL1QUARTER))ANDYEAR(`字段`)=YEAR(NOW());

查询上半年(六个月)的数据

SELECT*FROM`表名`WHERE`字段`BETWEENDATE_SUB(NOW(),INTERVAL6MONTH)ANDNOW();

查询今年的数据

SELECT*FROM`表名`WHEREDATE_FORMAT(`字段`,'%Y')=DATE_FORMAT(NOW(),'%Y');
SELECT*FROM`表名`WHEREDATE_FORMAT(`字段`,'%Y')-DATE_FORMAT(NOW(),'%Y')=0;
SELECT*FROM`表名`WHEREYEAR(NOW())=YEAR(`字段`);
SELECT*FROM`表名`WHEREYEAR(NOW())-YEAR(`字段`)=0;

查询去年的数据

SELECT*FROM`表名`WHEREDATE_FORMAT(`字段`,'%Y')-DATE_FORMAT(NOW(),'%Y')=-1;
SELECT*FROM`表名`WHEREYEAR(NOW())=YEAR(`字段`)+1;
SELECT*FROM`表名`WHEREYEAR(NOW())-YEAR(`字段`)=1;

到此这篇关于Mysql之日期查询的详细介绍的文章就介绍到这了,更多相关Mysql之日期查询内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

Mysql日期查询的详细介绍

热门推荐

随机推荐