mysql日期函数总结

太过爱你忘了你带给我的痛 2021-09-24 17:44 450阅读 0赞

文中代码未特殊说明时使用的mysql版本为5.7  
文档地址：[https://www.mysqlzh.com/doc/118.html][https_www.mysqlzh.com_doc_118.html]

### 日期格式化、字符串转日期 ###

select date_format(now(), '%Y-%m-%d %H:%i:%s');
    select str_to_date('2020-09-24 15:20:23', '%Y-%m-%d %H:%i:%s');
    select from_unixtime(unix_timestamp(now()), '%Y-%m-%d %H:%i:%s');

date\_format全部符号

<table> 
 <thead> 
  <tr> 
   <th>说明符</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>%a</td> 
   <td align="left">工作日的缩写名称 (Sun…Sat)</td> 
  </tr> 
  <tr> 
   <td>%b</td> 
   <td align="left">月份的缩写名称 (Jan…Dec)</td> 
  </tr> 
  <tr> 
   <td>%c</td> 
   <td align="left">月份，数字形式(0…12)</td> 
  </tr> 
  <tr> 
   <td>%D</td> 
   <td align="left">带有英语后缀的该月日期 (0th, 1st, 2nd, 3rd, …)</td> 
  </tr> 
  <tr> 
   <td>%d</td> 
   <td align="left">该月日期, 数字形式 (00…31)</td> 
  </tr> 
  <tr> 
   <td>%e</td> 
   <td align="left">该月日期, 数字形式(0…31)</td> 
  </tr> 
  <tr> 
   <td>%f</td> 
   <td align="left">微秒 (000000…999999)</td> 
  </tr> 
  <tr> 
   <td>%H</td> 
   <td align="left">小时(00…23)</td> 
  </tr> 
  <tr> 
   <td>%h</td> 
   <td align="left">小时(01…12)</td> 
  </tr> 
  <tr> 
   <td>%I</td> 
   <td align="left">小时 (01…12)</td> 
  </tr> 
  <tr> 
   <td>%i</td> 
   <td align="left">分钟,数字形式 (00…59)</td> 
  </tr> 
  <tr> 
   <td>%j</td> 
   <td align="left">一年中的天数 (001…366)</td> 
  </tr> 
  <tr> 
   <td>%k</td> 
   <td align="left">小时 (0…23)</td> 
  </tr> 
  <tr> 
   <td>%l</td> 
   <td align="left">小时 (1…12)</td> 
  </tr> 
  <tr> 
   <td>%M</td> 
   <td align="left">月份名称 (January…December)</td> 
  </tr> 
  <tr> 
   <td>%m</td> 
   <td align="left">月份, 数字形式 (00…12)</td> 
  </tr> 
  <tr> 
   <td>%p</td> 
   <td align="left">上午（AM）或下午（ PM）</td> 
  </tr> 
  <tr> 
   <td>%r</td> 
   <td align="left">时间 , 12小时制 (小时hh:分钟mm:秒数ss 后加 AM或PM)</td> 
  </tr> 
  <tr> 
   <td>%S</td> 
   <td align="left">秒 (00…59)</td> 
  </tr> 
  <tr> 
   <td>%s</td> 
   <td align="left">秒 (00…59)</td> 
  </tr> 
  <tr> 
   <td>%T</td> 
   <td align="left">时间 , 24小时制 (小时hh:分钟mm:秒数ss)</td> 
  </tr> 
  <tr> 
   <td>%U</td> 
   <td align="left">周 (00…53), 其中周日为每周的第一天</td> 
  </tr> 
  <tr> 
   <td>%u</td> 
   <td align="left">周 (00…53), 其中周一为每周的第一天</td> 
  </tr> 
  <tr> 
   <td>%V</td> 
   <td align="left">周 (01…53), 其中周日为每周的第一天 ; 和 %X同时使用</td> 
  </tr> 
  <tr> 
   <td>%v</td> 
   <td align="left">周 (01…53), 其中周一为每周的第一天 ; 和 %x同时使用</td> 
  </tr> 
  <tr> 
   <td>%W</td> 
   <td align="left">工作日名称 (周日…周六)</td> 
  </tr> 
  <tr> 
   <td>%w</td> 
   <td align="left">一周中的每日 (0=周日…6=周六)</td> 
  </tr> 
  <tr> 
   <td>%X</td> 
   <td align="left">该周的年份，其中周日为每周的第一天, 数字形式,4位数;和%V同时使用</td> 
  </tr> 
  <tr> 
   <td>%x</td> 
   <td align="left">该周的年份，其中周一为每周的第一天, 数字形式,4位数;和%v同时使用</td> 
  </tr> 
  <tr> 
   <td>%Y</td> 
   <td align="left">年份, 数字形式,4位数</td> 
  </tr> 
  <tr> 
   <td>%y</td> 
   <td align="left">年份, 数字形式 (2位数)</td> 
  </tr> 
  <tr> 
   <td>%%</td> 
   <td align="left">‘%’文字字符</td> 
  </tr> 
 </tbody> 
</table>

### 获取时间 ###

常用`now()`，在一个单一询问中，对诸如NOW() 的函数多次访问总是会得到同样的结果，这项原则也适用于 `CURDATE()、 CURTIME()、 UTC_DATE()、 UTC_TIME()、UTC_TIMESTAMP()`，以及所有和它们意义相同的函数。于此相对应的是`SYSDATE()`。

SELECT NOW(),SYSDATE(),SLEEP(3),NOW(),SYSDATE();

![在这里插入图片描述][202009241421395.png_pic_center]

#### 常用获取时间函数的区别 ####

*  now()：返回当前时间，格式为YYYY-MM-DD HH:MM:SS，在一个单一询问中总是会得到同样的结果。
 *  sysdate()：与now()大致相同，区别在于同一查询中可能返回不同值（见上节）
 *  curdate()：与now()大致相同，区别在于格式为YYYY-MM-DD

对于返回值，系统会根据语境为数字或字符串而返回不同的默认格式。比如now()在默认（字符串语境下）的返回格式为YYYY-MM-DD HH:MM:SS，但是在数字语境下则为YYYYMMDDHHMMSS。

mysql> SELECT NOW();
            -> '2020-09-24 22:59:21'
    
    mysql> SELECT NOW() + 0;
            -> 20200924230005
    
    mysql> SELECT NOW() + NOW();
            -> 40401848460050

具体规则就是将默认格式中的空格与符号消除，并用剩下的数字进行计算。所以mysql无法简单地对时间进行计算，而是要借助相应的时间函数。

### 日期计算 ###

#### 增加日期 ####

增加日期date\_add，减少日期date\_sub，以及他们的同义词adddate、subdate。

个人建议只使用date\_add即可，因为每一个函数都可以轻松取代其他三个，并且同一功能却要记三个函数实在有些变态。

SELECT date_add('2020-09-24', INTERVAL 1 DAY) as "增加一天",
           date_add(now(), INTERVAL -1 DAY) as "减少一天",
    	   date_add('2020-09-24', INTERVAL (1 + substr(version(), 1, 1) * 2) DAY) as "使用函数表达式"
    ;

date\_add中是可以使用函数表达式的，但是只可以使用整数，否则系统会自动四色五入为整数进行计算。

select case when date_add(now(), INTERVAL 0.4 DAY) = now() then '相等' else '不相等' end;#相等

所以对于每种类型的时间格式，都需要指定类型进行计算。

select date_add(now(), interval 1 day); 
    select date_add(now(), interval 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 '2020-09-25' + INTERVAL 1 DAY,
    	   now() + INTERVAL 1 DAY
    ;

这回连date\_add都没了，具体用哪种，自己选择吧。

#### 求日期差 ####

主要用到三个函数  
DATEDIFF、TIMEDIFF、TIMESTAMPDIFF。

DATEDIFF只能返回整数天，TIMEDIFF对参数限制过于严格，并且返回格式为`hh24:mi:ss`，范围还限制在了`-838:59:59到838:59:59`，实在鸡肋。

`TIMESTAMPDIFF`在使用上有些麻烦，但至少可以实现功能。

#求出精确到小时的天
    SELECT TIMESTAMPDIFF(hour, str_to_date('2020-09-24', '%Y-%m-%d'), now()) / 24;

[https_www.mysqlzh.com_doc_118.html]: https://www.mysqlzh.com/doc/118.html
[202009241421395.png_pic_center]: /images/20210923/ed8824d3149e4a03be4dd9e2b002dd95.png