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

àì夳堔傛蜴生んèń 2022-04-17 05:21 1152阅读 0赞

> 今天    
> 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 d**ate\_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 参数可以是下列值：

**日期、时间相减函数：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');