但行好事
莫论前程❤

MySQL数据库系列(18-1)–根据日期查询数据

知识点实践:

mysql> select now();
--> 2019-05-15 14:53:22 
mysql> select curdate();
--> 2019-05-15
mysql> select curtime();
--> 15:23:32 

mysql> select date_format(now(),'%Y');
--> 2019
mysql> select date_format(now(),'%Y:%m');
--> 2019:05
mysql> select to_days(now());
--> 737559
mysql> select curdate();
--> 2019-05-15
  • 今天
select * from 表名 where to_days(时间字段名) = to_days(now());
  • 昨天
SELECT * FROM 表名 WHERE TO_DAYS(`时间字段名`) = TO_DAYS(NOW()) - 1;  
  • 近7天
SELECT * FROM 表名 where date_sub(curdate(), interval 7 day) <= date(时间字段名)
  • 近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
  • 本月
SELECT * FROM 表名 WHERE date_format(时间字段名, '%Y%m') = date_format(CURDATE(),'%Y%m');
  • 上一月
SELECT * FROM 表名 WHERE period_diff(date_format(now(), '%Y%m'), date_format(时间字段名, '%Y%m')) =1
  • 查询本季度数据
select * from `ht_invoice_information` where quarter(create_date)= quarter(now());
  • 查询上季度数据
select * from `ht_invoice_information` where quarter(create_date)=quarter(date_sub(now(),interval 1 quarter));
  • 查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
  • 查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
  • 查询当前这周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
  • 查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
  • 查询上个月的数据
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 

select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) 

select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 

select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 

select * from user where pudate between  上月最后一天  and 下月第一天
  • 查询当前月份的数据
select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
  • 查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
  • 查询某个月的数据(查询17年10月份数据)
select * from exam   where date_format(starttime,'%Y-%m')='2017-10'
select * from exam   where date_format(starttime,'%Y-%m')=date_format('2017-10-05','%Y-%m')
赞(1) 打赏
未经允许不得转载:刘鹏博客 » MySQL数据库系列(18-1)–根据日期查询数据
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏