MySQL查询昨天、今天、近30天、上周、去年函数

置顶 于 2018-11-13 18:05:47 发布
阅读量1.5k 收藏 8
点赞数 2
分类专栏： Mysql Java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_38270106/article/details/84031631
版权

今天  
select * from 表名 where to_days(时间字段名) = to_days(now());  
昨天  
SELECT * FROM 表名 WHERE TO_DAYS(NOW( ) ) - TO_DAYS( 时间字段名) <= 1;
7天  
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名) ;
大于3天
select * from 表名 where datediff( dd, order_addtime, now() ) > 3;
近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 表名 where QUARTER(create_date) = QUARTER(now());  
#查询上季度数据  
select * from 表名 where QUARTER(create_date) = QUARTER(DATE_SUB(now(),interval 1 QUARTER));  
#查询本年数据  
select * from 表名 where YEAR(create_date) = YEAR(NOW());  
#查询上年数据  
select * from 表名 where year(create_date) = year(date_sub(now(), interval 1 year));  
查询当前这周的数据   
SELECT * FROM 表名 WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());  
查询上周的数据  
SELECT * FROM 表名 WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now()) - 1;  
查询当前月份的数据  
select * from 表名 where date_format(submittime, '%Y-%m') = date_format(now(), '%Y-%m')  
查询距离当前现在6个月的数据  
select * from 表名 where submittime between date_sub(now(), interval 6 month) and now();  
查询上个月的数据  
select * from 表名 where date_format(submittime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH), '%Y-%m')  
select * from 表名 where DATE_FORMAT(pudate, ' %Y%m ' ) = DATE_FORMAT(CURDATE(), '%Y%m') ;  
select * from 表名 where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())  
select * from 表名 where MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())  
select * from 表名 where YEAR (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = YEAR (now()) and MONTH (FROM_UNIXTIME(pudate, '%y-%m-%d')) = MONTH(now())  
select * from 表名 where pudate between 上月最后一天 and 下月第一天  

返回当前的日期和时间

select MICROSECOND(SYSDATE()); --0

select SECOND(SYSDATE()); --33

select MINUTE(SYSDATE()); --8

select HOUR(SYSDATE()); --18

select week(SYSDATE()); --45

select MONTH(SYSDATE()); --11

select MONTHNAME(SYSDATE()); --November

select QUARTER(SYSDATE()); --4

select year(SYSDATE()); --2018

select SYSDATE(); --2018-11-13 18:08:33

select NOW(); --2018-11-13 18:08:33

select CURDATE(); --2018-11-13

select CURTIME(); --18:08:33

返回当前日期和时间的UNIX时间戳

select UNIX_TIMESTAMP(); --1542099027

select UNIX_TIMESTAMP(SYSDATE()); --1542099027

select FROM_UNIXTIME(unix_timestamp(sysdate())); --2018-11-13 16:50:27

select FROM_UNIXTIME(unix_timestamp(sysdate()), '%Y-%m-%d %H:%i:%S'); --2018-11-13 16:50:28

select FROM_UNIXTIME(unix_timestamp(sysdate())-20, '%Y-%m-%d %H:%i:%S'); --2018-11-13 16:50:08

参考格式如下：

  • %M 月名字(January……December)
  • %W 星期名字(Sunday……Saturday)
  • %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
  • %Y 年, 数字, 4 位
  • %y 年, 数字, 2 位
  • %a 缩写的星期名字(Sun……Sat)
  • %d 月份中的天数, 数字(00……31)
  • %e 月份中的天数, 数字(0……31)
  • %m 月, 数字(01……12)
  • %c 月, 数字(1……12)
  • %b 缩写的月份名字(Jan……Dec)
  • %j 一年中的天数(001……366)
  • %H 小时(00……23)
  • %k 小时(0……23)
  • %h 小时(01……12)
  • %I 小时(01……12)
  • %l 小时(1……12)
  • %i 分钟, 数字(00……59)
  • %r 时间,12 小时(hh:mm:ss [AP]M)
  • %T 时间,24 小时(hh:mm:ss)
  • %S 秒(00……59)
  • %s 秒(00……59)
  • %p AM或PM
  • %w 一个星期中的天数(0=Sunday ……6=Saturday ）
  • %U 星期(0……52), 这里星期天是星期的第一天
  • %u 星期(0……52), 这里星期一是星期的第一天
  • %% 一个文字“%”。

用这个函数可以帮助我们在时间戳中筛选出某些天的数据。

SELECT username, FROM_UNIXTIME(create_time, "%Y-%m-%d") AS dat
FROM `wp_user`
WHERE
create_time >= UNIX_TIMESTAMP('2017-11-29')
AND
create_time < UNIX_TIMESTAMP('2017-11-30')
GROUP BY dat;

这个查询可以让我们查出29号那一天的用户注册记录。

查询前20秒内的记录数量

select marketing_activity_id, ip_address, COUNT(ip_address) num 
from rod_redpacket_log t1
where t1.create_date between FROM_UNIXTIME(unix_timestamp(sysdate())-20, '%Y-%m-%d %H:%i:%S') and sysdate()
GROUP BY ip_address 

str_to_date （字符串转换为日期）函数：str_to_date(str, format) 

select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09

select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09

select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09

select str_to_date('08:09:30', '%H:%i:%s'); -- 08:09:30

select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

select STR_TO_DATE('2020-05-20 21:57:51', '%Y-%m-%d %H:%i:%s'); --2020-05-20 21:57:51

日期转换函数、时间转换函数 date_format(date,format), time_format(time,format)

select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s'); --20080808222301

select date_format(SYSDATE(), '%Y-%m-%d %H:%i:%s'); --2018-11-13 18:11:44

查询当月出现的记录数量

select phone_number, COUNT(phone_number) as num from table_name t1
WHERE DATE_FORMAT(t1.create_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m') 
group by PHONE_NUMBER; 

查询当天出现的记录数量

select phone_number, COUNT(phone_number) as num from table_name t1
WHERE DATE_FORMAT(t1.create_date, '%Y-%m-%d') = DATE_FORMAT(CURDATE(), '%Y-%m-%d') 
group by PHONE_NUMBER; 

查询当天某个时间段的数据

select * from rod_redpacket_log 
where DATE_FORMAT(create_date, '%Y%m') = DATE_FORMAT(CURDATE( ), '%Y%m') 
and DATE_FORMAT(create_date, '%H:%i') 
between DATE_FORMAT( STR_TO_DATE(#{startTimeScop}, '%H:%i'), '%H:%i') 
and DATE_FORMAT( STR_TO_DATE(#{endTimeScop}, '%H:%i'), '%H:%i') ;

MySQL 日期、时间转换函数：date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。  

格式    描述

  • %a    缩写星期名
  • %b    缩写月名
  • %c    月，数值
  • %D    带有英文前缀的月中的天
  • %d    月的天，数值(00-31)
  • %e    月的天，数值(0-31)
  • %f    微秒
  • %H    小时 (00-23)
  • %h    小时 (01-12)
  • %I    小时 (01-12)
  • %i    分钟，数值(00-59)
  • %j    年的天 (001-366)
  • %k    小时 (0-23)
  • %l    小时 (1-12)
  • %M    月名
  • %m    月，数值(00-12)
  • %p    AM 或 PM
  • %r    时间，12-小时（hh:mm:ss AM 或 PM）
  • %S    秒(00-59)
  • %s    秒(00-59)
  • %T    时间, 24-小时 (hh:mm:ss)
  • %U    周 (00-53) 星期日是一周的第一天
  • %u    周 (00-53) 星期一是一周的第一天
  • %V    周 (01-53) 星期日是一周的第一天，与 %X 使用
  • %v    周 (01-53) 星期一是一周的第一天，与 %x 使用
  • %W    星期名
  • %w    周的天 （0=星期日, 6=星期六）
  • %X    年，其中的星期日是周的第一天，4 位，与 %V 使用
  • %x    年，其中的星期一是周的第一天，4 位，与 %v 使用
  • %Y    年，4 位
  • %y    年，2 位   

TO_DAYS函数 返回一个天数 

SELECT TO_DAYS(‘1997-10-07′); --729669

如果要查询当前表中昨天的数据那么

select * from lito where to_days(now()) - to_days(create_time) = 1;

如果要查询今天出现的数据次数

select city_name,city_code,phone_number,COUNT(phone_number) num 
from rod_redpacket_log t1
where to_days(t1.create_date) = to_days(NOW())
group by activity_id,phone_number;

MySQL 拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second) 

select makedate(2001,31); -- '2001-01-31'

select makedate(2001,32); -- '2001-02-01'

select maketime(12,15,30); -- '12:15:30'

MySQL （时间、秒）转换函数：time_to_sec(time), sec_to_time(seconds) 

select time_to_sec('01:00:05'); -- 3605

select sec_to_time(3605); -- '01:00:05'

向日期添加指定的时间间隔：DATE_ADD(date,INTERVAL expr type)

 select date_add(now(), interval 1 day); -- add 1 day
select date_add(now(), interval 1 hour); -- add 1 hour
select date_add(now(), interval 1 minute); -- ...
select date_add(now(), interval 1 second);
select date_add(now(), interval 1 microsecond);
select date_add(now(), interval 1 week);
select date_add(now(), interval 1 month);
select date_add(now(), interval 1 quarter);
select date_add(now(), interval 1 year);

select date_add(now(), interval -1 day); -- sub 1 day

select date_add('2008-08-09 12:12:33', interval '01:15:30' hour_second); --2008-08-09 13:28:03
select date_add('2008-08-09 12:12:33', interval '1 01:15:30' day_second); --2008-08-10 13:28:03

type 参数可以是下列值：

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

 日期减去一个时间间隔：DATE_SUB(date,INTERVAL expr type)

select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second); --1997-12-30 22:58:59

select DATE_SUB(NOW(),INTERVAL 20 SECOND), now(); --2018-11-14 12:53:36，2018-11-14 12:53:56

查询前三分钟出现的记录数量

select *,count(ACTIVITY_ID) from rod_redpacket_log
where CREATE_DATE BETWEEN DATE_SUB(now(),INTERVAL 3 MINUTE) and now() 
GROUP BY ACTIVITY_ID; 

查询前30天的数据

select crt_time,* from marketing_activity where crt_time between DATE_SUB(now(), interval 30 day) and now() order by crt_time desc;

type 参数可以是下列值：

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

日期、时间相减函数：datediff(date1,date2), timediff(time1,time2) ，date1-date2返回天数，两个参数类型必须相同。

select datediff('2008-08-08', '2008-08-01'); -- 7

select datediff('2008-08-01', '2008-08-08'); -- -7

 时间戳（timestamp）转换、增、减函数

TIMESTAMPDIFF(unit,begin,end)，date2-date1返回时间差，两个参数类型必须相同。

select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01

select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00

# MySQL timestampadd() 函数类似于 date_add()。
select timestampdiff(year, '2002-05-01', '2001-01-01'); -- -1
select timestampdiff(day , '2002-05-01', '2001-01-01'); -- -485
select timestampdiff(hour, '2008-08-08 12:00:00', '2008-08-08 00:00:00'); -- -12

select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7

unit参数是确定(end-begin)的结果的单位，表示为整数。 以下是有效单位：

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

 MySQL 获得当前时间戳函数：current_timestamp, current_timestamp() 

select current_timestamp, current_timestamp(); --2018-11-13 17:23:56，2018-11-13 17:23:56 

MySQL 的 YEARWEEK 是获取年份和周数的一个函数，函数形式为 YEARWEEK(date[,mode])

例如 2010-3-14 ，礼拜天

SELECT YEARWEEK('2010-3-14') 返回 11
SELECT YEARWEEK('2010-3-14',1) 返回 10

其中第二个参数是 mode ，具体指的意思如下：

Mode    First day of week    Range    Week 1 is the first week …
0           Sunday                   0-53       with a Sunday in this year
1           Monday                  0-53       with more than 3 days this year
2           Sunday                   1-53       with a Sunday in this year
3           Monday                  1-53       with more than 3 days this year
4           Sunday                   0-53       with more than 3 days this year
5           Monday                   0-53       with a Monday in this year
6           Sunday                   1-53       with more than 3 days this year
7           Monday                  1-53        with a Monday in this 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(now(),'%Y-%m')

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

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

查询上月的数据

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m');

评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值