MySQL:日期时间函数-日期时间计算和转换

旧城等待， 2023-01-03 14:17 457阅读 0赞

**目录**

MySQL 日期与时间方面的函数

获得当前日期

时间转换

MySQL Str to Date （字符串转换为日期）函数：str\_to\_date(str, format)

MySQL （日期、天数）转换函数：to\_days(date), from\_days(days)

MySQL （时间、秒）转换函数：time\_to\_sec(time), sec\_to\_time(seconds)

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

MySQL （Unix 时间戳、日期）转换函数

FROM\_UNIXTIME(unix\_timestamp,format),DATE\_FORMAT(date,format)中format

MySQL 日期时间计算函数

MySQL 为日期增加一个时间间隔：date\_add()

MySQL 为日期减去一个时间间隔：date\_sub()

DATE\_ADD(date,INTERVAL expr unit) 与 DATE\_SUB(date,INTERVAL expr unit)

MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)

MySQL timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值。

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

MySQL 时区（timezone）转换函数

--------------------

# MySQL 日期与时间方面的函数 #

<table> 
 <thead> 
  <tr> 
   <th>函数名称</th> 
   <th>函数功能说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#ADDDATE" rel="nofollow"><code>ADDDATE()</code></a></td> 
   <td>添加日期</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#ADDTIME" rel="nofollow"><code>ADDTIME()</code></a></td> 
   <td>添加时间</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#CONVERT_TZ" rel="nofollow"><code>CONVERT_TZ()</code></a></td> 
   <td>转换不同时区</td> 
  </tr> 
  <tr> 
   <td><strong><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#CURDATE" rel="nofollow"><span style="color:#f33b45;"><code>CURDATE()</code></span></a></strong></td> 
   <td><span style="color:#f33b45;"><strong>返回当前日期</strong></span></td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#CURRENT_DATE" rel="nofollow"><code>CURRENT_DATE()</code>&nbsp;与&nbsp;<code>CURRENT_DATE</code></a></td> 
   <td>等同于&nbsp;<code>CURDATE()</code></td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#CURRENT_TIME2" rel="nofollow"><code>CURRENT_TIME()</code>&nbsp;与&nbsp;<code>CURRENT_TIME</code></a></td> 
   <td>等同于&nbsp;<code>CURTIME()</code></td> 
  </tr> 
  <tr> 
   <td><strong><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#CURRENT_TIMESTAMP" rel="nofollow"><span style="color:#f33b45;"><code>CURRENT_TIMESTAMP()</code>&nbsp;与&nbsp;<code>CURRENT_TIMESTAMP</code></span></a></strong></td> 
   <td><strong><span style="color:#f33b45;">等同于&nbsp;<code>NOW()</code></span></strong></td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#CURTIME" rel="nofollow"><code>CURTIME()</code></a></td> 
   <td>返回当前时间</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#DATE_ADD" rel="nofollow"><code>DATE_ADD()</code></a></td> 
   <td>添加两个日期</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#DATE_FORMAT" rel="nofollow"><code>DATE_FORMAT()</code></a></td> 
   <td>按指定方式格式化日期</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#DATE_SUB" rel="nofollow"><code>DATE_SUB()</code></a></td> 
   <td>求解两个日期的间隔</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#DATE" rel="nofollow"><code>DATE()</code></a></td> 
   <td>提取日期或日期时间表达式中的日期部分</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#DATEDIFF" rel="nofollow"><code>DATEDIFF()</code></a></td> 
   <td>求解两个日期的间隔</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#DAY" rel="nofollow"><code>DAY()</code></a></td> 
   <td>等同于&nbsp;<code>DAYOFMONTH()</code></td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#DAYNAME" rel="nofollow"><code>DAYNAME()</code></a></td> 
   <td>返回星期中某天的名称</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#DAYOFMONTH" rel="nofollow"><code>DAYOFMONTH()</code></a></td> 
   <td>返回一月中某天的序号（1-31）</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#DAYOFWEEK" rel="nofollow"><code>DAYOFWEEK()</code></a></td> 
   <td>返回参数所定影的一周中某天的索引值</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#DAYOFYEAR" rel="nofollow"><code>DAYOFYEAR()</code></a></td> 
   <td>返回一年中某天的序号（1-366）</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#EXTRACT" rel="nofollow"><code>EXTRACT</code></a></td> 
   <td>提取日期中的相应部分</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#FROM_DAYS" rel="nofollow"><code>FROM_DAYS()</code></a></td> 
   <td>将一个天数序号转变为日期值</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#FROM_UNIXTIME" rel="nofollow"><code>FROM_UNIXTIME()</code></a></td> 
   <td>将日期格式化为 UNIX 的时间戳</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#HOUR" rel="nofollow"><code>HOUR()</code></a></td> 
   <td>提取时间</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#LAST_DAY" rel="nofollow"><code>LAST_DAY</code></a></td> 
   <td>根据参数，返回月中最后一天</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#LOCALTIME" rel="nofollow"><code>LOCALTIME()</code>&nbsp;和&nbsp;<code>LOCALTIME</code></a></td> 
   <td>等同于&nbsp;<code>NOW()</code></td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#LOCALTIMESTAMP" rel="nofollow"><code>LOCALTIMESTAMP</code>&nbsp;和&nbsp;<code>LOCALTIMESTAMP()</code></a></td> 
   <td>等同于&nbsp;<code>NOW()</code></td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#MAKEDATE" rel="nofollow"><code>MAKEDATE()</code></a></td> 
   <td>基于给定参数年份和所在年中的天数序号，返回一个日期</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#MAKETIME" rel="nofollow"><code>MAKETIME</code></a></td> 
   <td><code>MAKETIME()</code></td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#MICROSECOND" rel="nofollow"><code>MICROSECOND()</code></a></td> 
   <td>返回参数所对应的毫秒数</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#MINUTE" rel="nofollow"><code>MINUTE()</code></a></td> 
   <td>返回参数对应的分钟数</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#MONTH" rel="nofollow"><code>MONTH()</code></a></td> 
   <td>返回传入日期所对应的月序数</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#MONTHNAME" rel="nofollow"><code>MONTHNAME()</code></a></td> 
   <td>返回月的名称</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#NOW" rel="nofollow"><code>NOW()</code></a></td> 
   <td>返回当前日期与时间</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#PERIOD_ADD" rel="nofollow"><code>PERIOD_ADD()</code></a></td> 
   <td>为年-月组合日期添加一个时段</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#PERIOD_DIFF" rel="nofollow"><code>PERIOD_DIFF()</code></a></td> 
   <td>返回两个时段之间的月份差值</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#QUARTER" rel="nofollow"><code>QUARTER()</code></a></td> 
   <td>返回日期参数所对应的季度序号</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#SEC_TO_TIME" rel="nofollow"><code>SEC_TO_TIME()</code></a></td> 
   <td>将描述转变成 'HH:MM:SS' 的格式</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#SECOND" rel="nofollow"><code>SECOND()</code></a></td> 
   <td>返回秒序号（0-59）</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#STR_TO_DATE" rel="nofollow"><code>STR_TO_DATE()</code></a></td> 
   <td>将字符串转变为日期</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#SUBDATE" rel="nofollow"><code>SUBDATE()</code></a></td> 
   <td>三个参数的版本相当于&nbsp;<code>DATE_SUB()</code></td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#SUBTIME" rel="nofollow"><code>SUBTIME()</code></a></td> 
   <td>计算时间差值</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#SYSDATE" rel="nofollow"><code>SYSDATE()</code></a></td> 
   <td>返回函数执行时的时间</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#TIME_FORMAT" rel="nofollow"><code>TIME_FORMAT()</code></a></td> 
   <td>提取参数中的时间部分</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#TIME_TO_SEC" rel="nofollow"><code>TIME_TO_SEC()</code></a></td> 
   <td>将参数转化为秒数</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#TIME" rel="nofollow"><code>TIME()</code></a></td> 
   <td>提取传入表达式的时间部分</td> 
  </tr> 
  <tr> 
   <td><strong><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#TIMEDIFF" rel="nofollow"><span style="color:#f33b45;"><code>TIMEDIFF()</code></span></a></strong></td> 
   <td><strong><span style="color:#f33b45;">计算时间差值</span></strong></td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#TIMESTAMP" rel="nofollow"><code>TIMESTAMP()</code></a></td> 
   <td>单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#TIMESTAMPADD" rel="nofollow"><code>TIMESTAMPADD()</code></a></td> 
   <td>为日期时间表达式添加一个间隔 INTERVAL</td> 
  </tr> 
  <tr> 
   <td><strong><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#TIMESTAMPDIFF" rel="nofollow"><span style="color:#f33b45;"><code>TIMESTAMPDIFF()</code></span></a></strong></td> 
   <td><strong><span style="color:#f33b45;">从日期时间表达式中减去一个间隔 INTERVAL</span></strong></td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#TO_DAYS" rel="nofollow"><code>TO_DAYS()</code></a></td> 
   <td>返回转换成天数的日期参数</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#UNIX_TIMESTAMP" rel="nofollow"><code>UNIX_TIMESTAMP()</code></a></td> 
   <td>返回一个 UNIX 时间戳</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#UTC_DATE" rel="nofollow"><code>UTC_DATE()</code></a></td> 
   <td>返回当前的 UTC 日期</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#UTC_TIME" rel="nofollow"><code>UTC_TIME()</code></a></td> 
   <td>返回当前的 UTC 时间</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#UTC_TIMESTAMP" rel="nofollow"><code>UTC_TIMESTAMP()</code></a></td> 
   <td>返回当前的 UTC 时间与日期</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#WEEK" rel="nofollow"><code>WEEK()</code></a></td> 
   <td>返回周序号</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#WEEKDAY" rel="nofollow"><code>WEEKDAY()</code></a></td> 
   <td>返回某天在星期中的索引值</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#WEEKOFYEAR" rel="nofollow"><code>WEEKOFYEAR()</code></a></td> 
   <td>返回日期所对应的星期在一年当中的序号（1-53）</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#YEAR" rel="nofollow"><code>YEAR()</code></a></td> 
   <td>返回年份</td> 
  </tr> 
  <tr> 
   <td><a href="https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#YEARWEEK" rel="nofollow"><code>YEARWEEK()</code></a></td> 
   <td>返回年份及星期序号</td> 
  </tr> 
 </tbody> 
</table>

# 获得当前日期 #

select now(),
    current_timestamp(),
    current_timestamp,
    CURDATE(),
    CURTIME();

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZseTkxMDkwNQ_size_16_color_FFFFFF_t_70][]

# 时间转换 #

## MySQL 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

## MySQL （日期、天数）转换函数：to\_days(date), from\_days(days) ##

select to_days('0000-00-00'); -- 0
    select to_days('2008-08-08'); -- 733627

## 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'

## 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 （Unix 时间戳、日期）转换函数 ##

> unix\_timestamp(),  
> unix\_timestamp(date),  
> from\_unixtime(unix\_timestamp),  
> from\_unixtime(unix\_timestamp,format)

下面是示例：

select unix_timestamp(); -- 1218290027
    select unix_timestamp('2008-08-08'); -- 1218124800
    select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800
    
    select from_unixtime(1218290027); -- '2008-08-09 21:53:47'
    select from_unixtime(1218124800); -- '2008-08-08 00:00:00'
    select from_unixtime(1218169800); -- '2008-08-08 12:30:00'

## FROM\_UNIXTIME(unix\_timestamp,format),DATE\_FORMAT(date,format)中format ##

下表中列出了一些可用于 format 字符串的标识符。格式标识符第一个字符必须是`%`字符。

<table> 
 <thead> 
  <tr> 
   <th>格式标识符</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>%a</code></td> 
   <td>一星期中每天名称的缩写（Sun...Sat）</td> 
  </tr> 
  <tr> 
   <td><code>%b</code></td> 
   <td>月份的缩写（Jan...Dec）</td> 
  </tr> 
  <tr> 
   <td><code>%c</code></td> 
   <td>月份的数字表现形式（0...12）</td> 
  </tr> 
  <tr> 
   <td><code>%D</code></td> 
   <td>带有英语后缀的一个月中的每一天的名称（0th、1st、2nd、3rd）</td> 
  </tr> 
  <tr> 
   <td><code>%d</code></td> 
   <td>用数字形式表现的每月中的每一天（00...31）</td> 
  </tr> 
  <tr> 
   <td><code>%e</code></td> 
   <td>用数字形式表现的每月中的每一天（0...31）</td> 
  </tr> 
  <tr> 
   <td><code>%f</code></td> 
   <td>毫秒（000000...999999）</td> 
  </tr> 
  <tr> 
   <td><code>%H</code></td> 
   <td>24时制显示的小时（00...23）</td> 
  </tr> 
  <tr> 
   <td><code>%h</code></td> 
   <td>12时制显示的小时（01...12）</td> 
  </tr> 
  <tr> 
   <td><code>%I</code></td> 
   <td>12时制显示的小时（01...12）</td> 
  </tr> 
  <tr> 
   <td><code>%i</code></td> 
   <td>以数字形式表现的分钟数（00...59）</td> 
  </tr> 
  <tr> 
   <td><code>%j</code></td> 
   <td>一年中的每一天（001...366）</td> 
  </tr> 
  <tr> 
   <td><code>%k</code></td> 
   <td>24时制小时的另一种表现格式（0...23）</td> 
  </tr> 
  <tr> 
   <td><code>%l</code></td> 
   <td>12时制小时的另一种表现格式（1...12）</td> 
  </tr> 
  <tr> 
   <td><code>%M</code></td> 
   <td>用完整英文名称表示的月份（January...December）</td> 
  </tr> 
  <tr> 
   <td><code>%m</code></td> 
   <td>用数字表现的月份（00...12）</td> 
  </tr> 
  <tr> 
   <td><code>%p</code></td> 
   <td>上午（AM）或下午（PM）</td> 
  </tr> 
  <tr> 
   <td><code>%r</code></td> 
   <td>12时制的时间值（hh:mm:ss，后跟 AM 或 PM）</td> 
  </tr> 
  <tr> 
   <td><code>%S</code></td> 
   <td>秒（00...59）</td> 
  </tr> 
  <tr> 
   <td><code>%s</code></td> 
   <td>秒（00...59）</td> 
  </tr> 
  <tr> 
   <td><code>%T</code></td> 
   <td>24时制的小时（hh:mm:ss）</td> 
  </tr> 
  <tr> 
   <td><code>%U</code></td> 
   <td>星期（00...53），其中星期天是每星期的开始日</td> 
  </tr> 
  <tr> 
   <td><code>%u</code></td> 
   <td>星期（00...53），其中星期一是每星期的开始日</td> 
  </tr> 
  <tr> 
   <td><code>%V</code></td> 
   <td>星期（01...53），其中星期天是每星期的开始日，和&nbsp;<code>%X</code>&nbsp;一起使用</td> 
  </tr> 
  <tr> 
   <td><code>%v</code></td> 
   <td>星期（01...53），其中星期一是每星期的开始日，和&nbsp;<code>%x</code>&nbsp;一起使用</td> 
  </tr> 
  <tr> 
   <td><code>%W</code></td> 
   <td>一星期中各日名称（Sunday...Saturday）</td> 
  </tr> 
  <tr> 
   <td><code>%w</code></td> 
   <td>一星期中各日名称（0代表星期日，6代表星期六，以此类推）</td> 
  </tr> 
  <tr> 
   <td><code>%X</code></td> 
   <td>某星期所处年份。其中，星期天是每星期的开始日，采用4位数字形式表现，和&nbsp;<code>%V</code>一起使用</td> 
  </tr> 
  <tr> 
   <td><code>%x</code></td> 
   <td>某星期所处年份。其中，星期一是每星期的开始日，采用4位数字形式表现，和&nbsp;<code>%V</code>&nbsp;一起使用</td> 
  </tr> 
  <tr> 
   <td><code>%Y</code></td> 
   <td>4位数字表示的年份</td> 
  </tr> 
  <tr> 
   <td><code>%y</code></td> 
   <td>2位数字表示的年份</td> 
  </tr> 
  <tr> 
   <td><code>%%</code></td> 
   <td>符号<code>%</code>的字面值</td> 
  </tr> 
  <tr> 
   <td>%x（x为斜体）</td> 
   <td>字符x的字面值，x指以上未列出的任何字符</td> 
  </tr> 
 </tbody> 
</table>

# MySQL 日期时间计算函数 #

## MySQL 为日期增加一个时间间隔：date\_add() ##

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

## MySQL 为日期减去一个时间间隔：date\_sub()  ##

## DATE\_ADD(date,INTERVAL expr unit) 与 DATE\_SUB(date,INTERVAL expr unit) ##

执行日期计算的两种函数。`date` 是一个用来指定**开始日期**的 DATETIME 或 DATE 值。`expr` 是一种以字符串形式呈现的表达式，用来指定从开始日期增加或减少的间隔值。如果是负的间隔值，则 `expr` 值的第一个字符是`-`号。`unit` 是一个单位关键字，用来指定expr表达式应该采取的单位。

INTERVAL 关键字与单位说明符都不区分大小写。

下表列出了每个单位数值所对应的 expr 参数的期望格式。

<table> 
 <thead> 
  <tr> 
   <th>单位所能取的值</th> 
   <th>期望的expr格式</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>MICROSECOND</code></td> 
   <td>毫秒</td> 
  </tr> 
  <tr> 
   <td><code>SECOND</code></td> 
   <td>秒</td> 
  </tr> 
  <tr> 
   <td><code>MINUTE</code></td> 
   <td>分</td> 
  </tr> 
  <tr> 
   <td><code>HOUR</code></td> 
   <td>小时</td> 
  </tr> 
  <tr> 
   <td><code>DAY</code></td> 
   <td>日</td> 
  </tr> 
  <tr> 
   <td><code>WEEK</code></td> 
   <td>周</td> 
  </tr> 
  <tr> 
   <td><code>MONTH</code></td> 
   <td>月</td> 
  </tr> 
  <tr> 
   <td><code>QUARTER</code></td> 
   <td>季度</td> 
  </tr> 
  <tr> 
   <td><code>YEAR</code></td> 
   <td>年</td> 
  </tr> 
  <tr> 
   <td><code>SECOND_MICROSECOND</code></td> 
   <td>'秒.毫秒'</td> 
  </tr> 
  <tr> 
   <td><code>MINUTE_MICROSECOND</code></td> 
   <td>'分.毫秒'</td> 
  </tr> 
  <tr> 
   <td><code>MINUTE_SECOND</code></td> 
   <td>'分:秒'</td> 
  </tr> 
  <tr> 
   <td><code>HOUR_MICROSECOND</code></td> 
   <td>'小时.毫秒'</td> 
  </tr> 
  <tr> 
   <td><code>HOUR_SECOND</code></td> 
   <td>'小时:分:秒'</td> 
  </tr> 
  <tr> 
   <td><code>HOUR_MINUTE</code></td> 
   <td>'小时:分'</td> 
  </tr> 
  <tr> 
   <td><code>DAY_MICROSECOND</code></td> 
   <td>'日.毫秒'</td> 
  </tr> 
  <tr> 
   <td><code>DAY_SECOND</code></td> 
   <td>'日 小时:分:秒'</td> 
  </tr> 
  <tr> 
   <td><code>DAY_MINUTE</code></td> 
   <td>'日 小时:分'</td> 
  </tr> 
  <tr> 
   <td><code>DAY_HOUR</code></td> 
   <td>'日 小时'</td> 
  </tr> 
  <tr> 
   <td><code>YEAR_MONTH</code></td> 
   <td>'年-月'</td> 
  </tr> 
 </tbody> 
</table>

# MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2) #

## MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2) ##

-- datediff(date1,date2)：两个日期相减 date1 - date2，返回天数。
    select datediff('2008-08-08', '2008-08-01'); -- 7
    select datediff('2008-08-01', '2008-08-08'); -- -7

## MySQL timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值。 ##

select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
    select timediff('08:08:08', '00:00:00'); -- 08:08:08

> 注意：timediff(time1,time2) 函数的两个参数类型必须相同。

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

>     timestamp(date) -- date to timestamp
>     timestamp(dt,time) -- dt + time
>     timestampadd(unit,interval,datetime_expr) --
>     timestampdiff(unit,datetime_expr1,datetime_expr2) --

请看示例部分：

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
    
    -- 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

> MySQL timestampdiff() 函数就比 datediff() 功能强多了，datediff() 只能计算两个日期（date）之间相差的天数。

# MySQL 时区（timezone）转换函数 #

-- convert_tz(dt,from_tz,to_tz)
    
    select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00

时区转换也可以通过 date\_add, date\_sub, timestampadd 来实现。

select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00
    select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00
    select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00

参考链接:[https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html][https_wiki.jikexueyuan.com_project_mysql_useful-functions_time-functions.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZseTkxMDkwNQ_size_16_color_FFFFFF_t_70]: /images/20221119/1c35a62216c4472c83f4347afdf6780a.png
[https_wiki.jikexueyuan.com_project_mysql_useful-functions_time-functions.html]: https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html#FROM_UNIXTIME