MySQL常见时间函数简介

我不是女神ヾ 2022-04-12 11:40 228阅读 0赞

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th>简介</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ADDDATE()</td> 
   <td>增加时间，天</td> 
  </tr> 
  <tr> 
   <td>ADDTIME()</td> 
   <td>增加时间，时分秒</td> 
  </tr> 
  <tr> 
   <td>CONVERT_TZ()</td> 
   <td>时区转换</td> 
  </tr> 
  <tr> 
   <td>CURDATE()</td> 
   <td>返回当前时间，date格式</td> 
  </tr> 
  <tr> 
   <td>CURTIME()</td> 
   <td>返回当前时间，time格式</td> 
  </tr> 
  <tr> 
   <td>CURRENT_TIMESTAMP()</td> 
   <td>返回当前时间，timestamp格式，与now()类似</td> 
  </tr> 
  <tr> 
   <td>DATE()</td> 
   <td>提取时间值的date部分</td> 
  </tr> 
  <tr> 
   <td>DATE_FORMAT()</td> 
   <td>指定时间值的格式</td> 
  </tr> 
  <tr> 
   <td>DATE_SUB()</td> 
   <td>减少指定时间</td> 
  </tr> 
  <tr> 
   <td>DATEDIFF()</td> 
   <td>时间差</td> 
  </tr> 
  <tr> 
   <td>DAY()</td> 
   <td>计算当月已过的天数，与dayofmonth类似</td> 
  </tr> 
  <tr> 
   <td>DAYNAME()</td> 
   <td>返回特定时间是星期几</td> 
  </tr> 
  <tr> 
   <td>DAYOFWEEK</td> 
   <td>根据时间是星期几，返回对应数字</td> 
  </tr> 
  <tr> 
   <td>DAYOFYEAR</td> 
   <td>根据时间，返回其是一年中的第几天</td> 
  </tr> 
  <tr> 
   <td>EXTRACT</td> 
   <td>抽取时间值的指定部分</td> 
  </tr> 
  <tr> 
   <td>HOUR()</td> 
   <td>提取时间值的hour部分</td> 
  </tr> 
  <tr> 
   <td>LAST_DAY()</td> 
   <td>返回当月最后一天</td> 
  </tr> 
  <tr> 
   <td>MONTHNAME()</td> 
   <td>返回月份名字</td> 
  </tr> 
  <tr> 
   <td>TIMEDIFF()</td> 
   <td>计算时间差</td> 
  </tr> 
 </tbody> 
</table>

1.ADDDATE  
语法：ADDDATE(date,INTERVAL expr unit)，ADDDATE(expr,date)  
用法：增加n天，与date\_add函数用法类似。

mysql> select adddate('2018-12-03',interval 1 day);
    +--------------------------------------+
    | adddate('2018-12-03',interval 1 day) |
    +--------------------------------------+
    | 2018-12-04                           |
    +--------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select date_add('2018-12-03',interval 1 day); 
    +---------------------------------------+
    | date_add('2018-12-03',interval 1 day) |
    +---------------------------------------+
    | 2018-12-04                            |
    +---------------------------------------+
    1 row in set (0.00 sec)
    mysql> select adddate('2018-12-03',1); 
    +-------------------------+
    | adddate('2018-12-03',1) |
    +-------------------------+
    | 2018-12-04              |
    +-------------------------+
    1 row in set (0.00 sec)

2.ADDTIME  
语法：ADDTIME(expr1,expr2)  
用法：将expr1增加expr2，返回结果。

mysql> select addtime('2018-12-03 09:56:00','00:04:00');
    +-------------------------------------------+
    | addtime('2018-12-03 09:56:00','00:04:00') |
    +-------------------------------------------+
    | 2018-12-03 10:00:00                       |
    +-------------------------------------------+
    1 row in set (0.00 sec)

3.CONVERT\_TZ  
语法：CONVERT\_TZ(date,from\_tz,to\_tz)  
用法：将时间从一个时区转换成另一个时区

mysql> SELECT CONVERT_TZ('2018-12-03 09:00:00','+00:00','+10:00');  
    +-----------------------------------------------------+
    | CONVERT_TZ('2018-12-03 09:00:00','+00:00','+10:00') |
    +-----------------------------------------------------+
    | 2018-12-03 19:00:00                                 |
    +-----------------------------------------------------+
    1 row in set (0.00 sec)

4.CURDATE  
语法：CURDATE()  
用法：返回当前时间，格式为’YYYY-MM-DD’或者YYYYMMDD。与current\_date()类似。

mysql> select curdate();
    +------------+
    | curdate()  |
    +------------+
    | 2018-12-03 |
    +------------+
    1 row in set (0.00 sec)
    
    mysql> select curdate()+0;
    +-------------+
    | curdate()+0 |
    +-------------+
    |    20181203 |
    +-------------+
    1 row in set (0.00 sec)

5.DATE  
语法：DATE(expr)  
用法：针对特定的时间值，抽取date部分。

mysql> select date('2018-12-04 09:00:00');
    +-----------------------------+
    | date('2018-12-04 09:00:00') |
    +-----------------------------+
    | 2018-12-04                  |
    +-----------------------------+
    1 row in set (0.00 sec)

6.DATE\_FORMAT  
语法：DATE\_FORMAT(date,format)  
用法：指定时间值的格式

mysql> select date_format('2018-12-03','%W %M %Y');
    +--------------------------------------+
    | date_format('2018-12-03','%W %M %Y') |
    +--------------------------------------+
    | Monday December 2018                 |
    +--------------------------------------+
    1 row in set (0.00 sec)

常用的格式有%Y年，%m月，%d天，%H时，%i分，%s秒，%W星期几。

7.DATE\_SUB  
语法：DATE\_SUB(date,INTERVAL expr unit)  
用法：将时间值减去对应的单位。

mysql> select date_sub('2018-12-03',interval 1 day);
    +---------------------------------------+
    | date_sub('2018-12-03',interval 1 day) |
    +---------------------------------------+
    | 2018-12-02                            |
    +---------------------------------------+
    1 row in set (0.00 sec)

8.DATEDIFF  
语法：DATEDIFF(expr1,expr2)  
用法：计算两个时间差，单位为天。

mysql> select datediff('2018-12-04 09:00:00','2018-12-03 10:00:00');
    +-------------------------------------------------------+
    | datediff('2018-12-04 09:00:00','2018-12-03 10:00:00') |
    +-------------------------------------------------------+
    |                                                     1 |
    +-------------------------------------------------------+
    1 row in set (0.00 sec)

9.DAY()  
语法：DAY(date)  
用法：计算某个时间值在当月已过的天数，与dayofmonth用法类似。

mysql> select day('2018-12-03');
    +-------------------+
    | day('2018-12-03') |
    +-------------------+
    |                 3 |
    +-------------------+
    1 row in set (0.00 sec)
    
    mysql> select dayofmonth('2018-12-03');
    +--------------------------+
    | dayofmonth('2018-12-03') |
    +--------------------------+
    |                        3 |
    +--------------------------+
    1 row in set (0.00 sec)

10.DAYNAME  
语法：DAYNAME(DATE)  
用法：针对特定的时间，返回其是星期几。

mysql> select dayname('2018-11-01');
    +-----------------------+
    | dayname('2018-11-01') |
    +-----------------------+
    | Thursday              |
    +-----------------------+
    1 row in set (0.00 sec)

11.DAYOFWEEK  
语法：DAYOFWEEK(date)  
用法：针对特定的时间，根据其是星期几，返回数字，如星期一返回2。

12.DAYOFYEAR  
语法：DAYOFYEAR(date)  
用法：根据给定的时间，返回其在一年中属于第几天。

mysql> select dayofyear('2018-12-03');
    +-------------------------+
    | dayofyear('2018-12-03') |
    +-------------------------+
    |                     337 |
    +-------------------------+
    1 row in set (0.00 sec)

13.EXTRACT  
语法：EXTRACT(unit from date)  
用法：在时间值中抽取指定的部分。

mysql> select extract(day from '2018-12-03 09:00:00');
    +-----------------------------------------+
    | extract(day from '2018-12-03 09:00:00') |
    +-----------------------------------------+
    |                                       3 |
    +-----------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select extract(year from '2018-12-03 09:00:00');   
    +------------------------------------------+
    | extract(year from '2018-12-03 09:00:00') |
    +------------------------------------------+
    |                                     2018 |
    +------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select extract(year_month from '2018-12-03 09:00:00');
    +------------------------------------------------+
    | extract(year_month from '2018-12-03 09:00:00') |
    +------------------------------------------------+
    |                                         201812 |
    +------------------------------------------------+
    1 row in set (0.00 sec)

14.LAST\_DAY()  
语法：LAST\_DAY(date)  
用法：返回给定时间的当月最后一天，如果时间格式错误，则返回null。

mysql> select last_day('2018-12-03');
    +------------------------+
    | last_day('2018-12-03') |
    +------------------------+
    | 2018-12-31             |
    +------------------------+
    1 row in set (0.00 sec)
    
    mysql> select last_day('2018-12-33');
    +------------------------+
    | last_day('2018-12-33') |
    +------------------------+
    | NULL                   |
    +------------------------+
    1 row in set, 1 warning (0.00 sec)

15.MONTHNAME()  
语法：MONTHNAME(date)  
用法：返回指定时间的月份名字。

mysql> select monthname('2018-12-03');
    +-------------------------+
    | monthname('2018-12-03') |
    +-------------------------+
    | December                |
    +-------------------------+
    1 row in set (0.00 sec)