MySQL日期和时间函数汇总

我不是女神ヾ 2022-12-27 05:00 268阅读 0赞

> 本文基于MySQL8.0

本文介绍MySQL关于日期和时间操作的函数。

日期和时间函数

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ADDDATE()</td> 
   <td>给日期值添加时间值</td> 
  </tr> 
  <tr> 
   <td>ADDTIME()</td> 
   <td>添加time</td> 
  </tr> 
  <tr> 
   <td>CONVERT_TZ()</td> 
   <td>从一个时区转换为另一个时区</td> 
  </tr> 
  <tr> 
   <td>CURDATE()</td> 
   <td>返回当前日期</td> 
  </tr> 
  <tr> 
   <td>CURRENT_DATE(), CURRENT_DATE</td> 
   <td>和CURDATE()同义</td> 
  </tr> 
  <tr> 
   <td>CURRENT_TIME(), CURRENT_TIME</td> 
   <td>和CURDATE()同义</td> 
  </tr> 
  <tr> 
   <td>CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP</td> 
   <td>和NOW() 同义</td> 
  </tr> 
  <tr> 
   <td>CURTIME()</td> 
   <td>返回当前时间</td> 
  </tr> 
  <tr> 
   <td>DATE()</td> 
   <td>获取日期或日期时间表达式的日期部分</td> 
  </tr> 
  <tr> 
   <td>DATE_ADD()</td> 
   <td>将时间值（间隔）添加到日期值</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>DAYOFMONTH()</td> 
   <td>返回月份的日期 (0-31)</td> 
  </tr> 
  <tr> 
   <td>DAYOFWEEK()</td> 
   <td>根据参数返回工作日的排序</td> 
  </tr> 
  <tr> 
   <td>DAYOFYEAR()</td> 
   <td>返回一年中的某一天 (1-366)</td> 
  </tr> 
  <tr> 
   <td>EXTRACT()</td> 
   <td>获取日期中的一部分</td> 
  </tr> 
  <tr> 
   <td>FROM_DAYS()</td> 
   <td>将一天的序号转化为日期</td> 
  </tr> 
  <tr> 
   <td>FROM_UNIXTIME()</td> 
   <td>将Unix时间戳格式化为日期</td> 
  </tr> 
  <tr> 
   <td>GET_FORMAT()</td> 
   <td>返回日期格式字符串</td> 
  </tr> 
  <tr> 
   <td>HOUR()</td> 
   <td>获取小时</td> 
  </tr> 
  <tr> 
   <td>LAST_DAY</td> 
   <td>返回参数月份的最后一天</td> 
  </tr> 
  <tr> 
   <td>LOCALTIME(), LOCALTIME</td> 
   <td>和NOW()同义</td> 
  </tr> 
  <tr> 
   <td>LOCALTIMESTAMP, LOCALTIMESTAMP()</td> 
   <td>和 NOW()同义</td> 
  </tr> 
  <tr> 
   <td>MAKEDATE()</td> 
   <td>从年和日创建日期</td> 
  </tr> 
  <tr> 
   <td>MAKETIME()</td> 
   <td>从时、分、秒创建时间</td> 
  </tr> 
  <tr> 
   <td>MICROSECOND()</td> 
   <td>从参数返回微秒</td> 
  </tr> 
  <tr> 
   <td>MINUTE()</td> 
   <td>返回参数分钟</td> 
  </tr> 
  <tr> 
   <td>MONTH()</td> 
   <td>返回已过日期起的月份</td> 
  </tr> 
  <tr> 
   <td>MONTHNAME()</td> 
   <td>返回月份名称</td> 
  </tr> 
  <tr> 
   <td>NOW()</td> 
   <td>返回当前日期和时间</td> 
  </tr> 
  <tr> 
   <td>PERIOD_ADD()</td> 
   <td>给年-月添加一段时间</td> 
  </tr> 
  <tr> 
   <td>PERIOD_DIFF()</td> 
   <td>返回一段时间之间的月数</td> 
  </tr> 
  <tr> 
   <td>QUARTER()</td> 
   <td>从日期参数返回季度</td> 
  </tr> 
  <tr> 
   <td>SEC_TO_TIME()</td> 
   <td>将秒转换为“hh:mm:ss”格式</td> 
  </tr> 
  <tr> 
   <td>SECOND()</td> 
   <td>返回秒 (0-59)</td> 
  </tr> 
  <tr> 
   <td>STR_TO_DATE()</td> 
   <td>将字符串转换为日期</td> 
  </tr> 
  <tr> 
   <td>SUBDATE()</td> 
   <td>当使用三个参数调用时，和DATE_SUB（）同义</td> 
  </tr> 
  <tr> 
   <td>SUBTIME()</td> 
   <td>减去时间</td> 
  </tr> 
  <tr> 
   <td>SYSDATE()</td> 
   <td>返回函数执行的时间</td> 
  </tr> 
  <tr> 
   <td>TIME()</td> 
   <td>根据传递的表达式获取时间</td> 
  </tr> 
  <tr> 
   <td>TIME_FORMAT()</td> 
   <td>格式为时间</td> 
  </tr> 
  <tr> 
   <td>TIME_TO_SEC()</td> 
   <td>返回转换为秒的参数</td> 
  </tr> 
  <tr> 
   <td>TIMEDIFF()</td> 
   <td>时间相减</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP()</td> 
   <td>对于单个参数，此函数返回日期或日期时间表达式；对于两个参数，返回参数的和</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMPADD()</td> 
   <td>向日期时间表达式添加间隔</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMPDIFF()</td> 
   <td>从日期时间表达式中减去间隔</td> 
  </tr> 
  <tr> 
   <td>TO_DAYS()</td> 
   <td>返回转换为天的日期参数</td> 
  </tr> 
  <tr> 
   <td>TO_SECONDS()</td> 
   <td>返回从0年起转换为秒的日期或日期时间参数</td> 
  </tr> 
  <tr> 
   <td>UNIX_TIMESTAMP()</td> 
   <td>返回Unix时间戳</td> 
  </tr> 
  <tr> 
   <td>UTC_DATE()</td> 
   <td>返回当前UTC日期</td> 
  </tr> 
  <tr> 
   <td>UTC_TIME()</td> 
   <td>返回当前UTC时间</td> 
  </tr> 
  <tr> 
   <td>UTC_TIMESTAMP()</td> 
   <td>返回当前UTC日期和时间</td> 
  </tr> 
  <tr> 
   <td>WEEK()</td> 
   <td>返回星期数</td> 
  </tr> 
  <tr> 
   <td>WEEKDAY()</td> 
   <td>返回工作日序号</td> 
  </tr> 
  <tr> 
   <td>WEEKOFYEAR()</td> 
   <td>返回日期（1-53）的日历周</td> 
  </tr> 
  <tr> 
   <td>YEAR()</td> 
   <td>返回年</td> 
  </tr> 
  <tr> 
   <td>YEARWEEK()</td> 
   <td>返回年份和星期</td> 
  </tr> 
 </tbody> 
</table>

来看一看MySQL常用日期和时间函数的示例。

## 获得当前日期时间 ##

*  CURDATE()  
    以“**YYYY-MM-DD**”或**YYYYMMDD**格式返回当前日期，具体取决于函数是在字符串上下文中使用还是在数字上下文中使用。

mysql> SELECT CURDATE();
            -> '2008-06-13'
    mysql> SELECT CURDATE() + 0;
            -> 20080613

*  CURTIME(\[fsp\])  
    以’**hh:mm:ss**’或**hhmmss**格式返回当前时间，具体取决于函数是在字符串上下文中使用还是在数字上下文中使用。该值以会话时区表示。

如果指定的**fsp**精度是从0到6的小数位数，则指定从0到6的小数精度。

mysql> SELECT CURTIME();
            -> '23:50:26'
    mysql> SELECT CURTIME() + 0;
            -> 235026.000000

*  NOW(\[fsp\])  
    以’YYYY-MM-DD hh:MM:ss’或YYYYMMDDhhmmss格式返回当前日期和时间，具体取决于函数是在字符串上下文中使用还是在数字上下文中使用。该值以会话时区表示。

mysql> SELECT NOW();
            -> '2007-12-15 23:50:26'
    mysql> SELECT NOW() + 0;
            -> 20071215235026.000000

## 时间日期格式转化 ##

同一个日期时间会有多种不同的表示方式，有的时候需要在不同格式之间相互转换。在MySQL中用的是date\_format()函数:

*  DATE\_FORMAT(date,format):根据格式字符串格式化日期值。

相关的格式如下，除了用于这个函数，格式还可以用于：STR\_TO\_DATE(), TIME\_FORMAT(), UNIX\_TIMESTAMP().

<table> 
 <thead> 
  <tr> 
   <th>格式</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>%a</td> 
   <td>缩写星期名 (Sun…Sat)</td> 
  </tr> 
  <tr> 
   <td>%b</td> 
   <td>缩写月名 (Jan…Dec)</td> 
  </tr> 
  <tr> 
   <td>%c</td> 
   <td>月, 数值 (0…12)</td> 
  </tr> 
  <tr> 
   <td>%D</td> 
   <td>带有英文前缀的月中的天 (0th, 1st, 2nd, 3rd, …)</td> 
  </tr> 
  <tr> 
   <td>%d</td> 
   <td>一个月里面的某一天, 数值 (00…31)</td> 
  </tr> 
  <tr> 
   <td>%e</td> 
   <td>一个月里面的某一天, 数值 (0…31)</td> 
  </tr> 
  <tr> 
   <td>%f</td> 
   <td>微秒 (000000…999999)</td> 
  </tr> 
  <tr> 
   <td>%H</td> 
   <td>小时 (00…23)</td> 
  </tr> 
  <tr> 
   <td>%h</td> 
   <td>小时 (01…12)</td> 
  </tr> 
  <tr> 
   <td>%I</td> 
   <td>小时 (01…12)</td> 
  </tr> 
  <tr> 
   <td>%i</td> 
   <td>分钟, 数值 (00…59)</td> 
  </tr> 
  <tr> 
   <td>%j</td> 
   <td>一年中的某一天 (001…366)</td> 
  </tr> 
  <tr> 
   <td>%k</td> 
   <td>小时 (0…23)</td> 
  </tr> 
  <tr> 
   <td>%l</td> 
   <td>小时 (1…12)</td> 
  </tr> 
  <tr> 
   <td>%M</td> 
   <td>月名 (January…December)</td> 
  </tr> 
  <tr> 
   <td>%m</td> 
   <td>月份, 数值 (00…12)</td> 
  </tr> 
  <tr> 
   <td>%p</td> 
   <td>AM or PM</td> 
  </tr> 
  <tr> 
   <td>%r</td> 
   <td>时间, 12 - 小时 (hh:mm:ss AM 或者 PM)</td> 
  </tr> 
  <tr> 
   <td>%S</td> 
   <td>秒 (00…59)</td> 
  </tr> 
  <tr> 
   <td>%s</td> 
   <td>秒 (00…59)</td> 
  </tr> 
  <tr> 
   <td>%T</td> 
   <td>时间, 24 - 小时 (hh:mm:ss)</td> 
  </tr> 
  <tr> 
   <td>%W</td> 
   <td>星期名 (Sunday…Saturday)</td> 
  </tr> 
  <tr> 
   <td>%w</td> 
   <td>周几 (0=Sunday…6=Saturday)</td> 
  </tr> 
  <tr> 
   <td>%Y</td> 
   <td>年, 数值, 4 个数字</td> 
  </tr> 
  <tr> 
   <td>%y</td> 
   <td>年, 数值, 2 个数字</td> 
  </tr> 
 </tbody> 
</table>

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
            -> 'Sunday October 2009'
    mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
            -> '22:23:00'
    mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
        ->                 '%D %y %a %d %m %b %j');
            -> '4th 00 Thu 04 10 Oct 277'
    mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
        ->                 '%H %k %I %r %T %S %w');
            -> '22 22 10 10:23:00 PM 22:23:00 00 6'
    mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
            -> '1998 52'
    mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
            -> '00'

## 日期时间运算 ##

*  DATE\_ADD(date,INTERVAL expr unit),
 *  DATE\_SUB(date,INTERVAL expr unit)  
    这些函数执行日期运算,来进行日期向前/向后的偏移。date参数指定开始日期或日期时间值。expr是一个表达式，指定从开始日期加上或减去的间隔值。expr被计算为一个字符串；它可以以-开头表示负间隔。unit是一个关键字，指示表达式应使用的单位。

mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
            -> '2018-05-02'
    mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
            -> '2017-05-01'
    mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
        ->                 INTERVAL 1 SECOND);
            -> '2021-01-01 00:00:00'
    mysql> SELECT DATE_ADD('2018-12-31 23:59:59',
        ->                 INTERVAL 1 DAY);
            -> '2019-01-01 23:59:59'
    mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
        ->                 INTERVAL '1:1' MINUTE_SECOND);
            -> '2101-01-01 00:01:00'
    mysql> SELECT DATE_SUB('2025-01-01 00:00:00',
        ->                 INTERVAL '1 1:1:1' DAY_SECOND);
            -> '2024-12-30 22:58:59'
    mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
        ->                 INTERVAL '-1 10' DAY_HOUR);
            -> '1899-12-30 14:00:00'
    mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
            -> '1997-12-02'
    mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
        ->            INTERVAL '1.999999' SECOND_MICROSECOND);
            -> '1993-01-01 00:00:01.000001'

*  DATEDIFF(expr1,expr2)  
    DATEDIFF（）返回expr1−expr2，以从一个日期到另一个日期的天数表示。expr1和expr2是日期或日期和时间表达式。计算中只使用值的日期部分。

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
            -> 1
    mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
            -> -31

**参考：**

【1】：[12.7 Date and Time Functions][]  
【2】：[ MySQL 获得当前日期时间 函数][MySQL _]  
【3】：[一文搞定Mysql日期时间函数][Mysql]

[12.7 Date and Time Functions]: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
[MySQL _]: https://www.cnblogs.com/ggjucheng/p/3352280.html
[Mysql]: https://cloud.tencent.com/developer/article/1579711