【数据库】Mysql日期/时间相关函数汇总

女爷i 2023-06-12 09:45 4阅读 0赞

当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，与数据库中日期列的格式相匹配。  
只要数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间，情况就有点复杂了。  
在讨论日期查询的复杂性之前，我们先来看看最重要的内建日期处理函数。

## MySQL Date 函数 ##

下面的表格列出了 MySQL 中最重要的内建日期函数：

<table> 
 <tbody> 
  <tr> 
   <th>函数</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td><a href="https://www.w3school.com.cn/sql/func_now.asp" rel="nofollow">NOW()</a></td> 
   <td>返回当前的日期和时间</td> 
  </tr> 
  <tr> 
   <td><a href="https://www.w3school.com.cn/sql/func_curdate.asp" rel="nofollow">CURDATE()</a></td> 
   <td>返回当前的日期</td> 
  </tr> 
  <tr> 
   <td><a href="https://www.w3school.com.cn/sql/func_curtime.asp" rel="nofollow">CURTIME()</a></td> 
   <td>返回当前的时间</td> 
  </tr> 
  <tr> 
   <td><a href="https://www.w3school.com.cn/sql/func_date.asp" rel="nofollow">DATE()</a></td> 
   <td>提取日期或日期/时间表达式的日期部分</td> 
  </tr> 
  <tr> 
   <td><a href="https://www.w3school.com.cn/sql/func_extract.asp" rel="nofollow">EXTRACT()</a></td> 
   <td>返回日期/时间按的单独部分</td> 
  </tr> 
  <tr> 
   <td><a href="https://www.w3school.com.cn/sql/func_date_add.asp" rel="nofollow">DATE_ADD()</a></td> 
   <td>给日期添加指定的时间间隔</td> 
  </tr> 
  <tr> 
   <td><a href="https://www.w3school.com.cn/sql/func_date_sub.asp" rel="nofollow">DATE_SUB()</a></td> 
   <td>从日期减去指定的时间间隔</td> 
  </tr> 
  <tr> 
   <td><a href="https://www.w3school.com.cn/sql/func_datediff_mysql.asp" rel="nofollow">DATEDIFF()</a></td> 
   <td>返回两个日期之间的天数</td> 
  </tr> 
  <tr> 
   <td><a href="https://www.w3school.com.cn/sql/func_date_format.asp" rel="nofollow">DATE_FORMAT()</a></td> 
   <td>用不同的格式显示日期/时间</td> 
  </tr> 
 </tbody> 
</table>

下面通过具体列子说明每个函数的使用方法：

# 当前完整时间
    select now() as 当前时间1;                 -- 2019-11-12 10:29:50
    select current_timestamp() as 当前时间2;   -- 2019-11-12 10:29:50
    
    
    # 当前日期或时间部分
    select current_date() as 当前日期部分1;    -- 2019-11-12 
    select CURDATE() as 当前日期部分2;         -- 2019-11-12 
    select DATE(now()) as 获取日期部分3;       -- 2019-11-12
    select current_time() as 当前时间部分1;    -- 10:28:58
    select CURTIME() as 当前时间部分2;         -- 10:28:58
    select time(now()) as 获取时间部分3;       -- 10:28:58
    
    
    # 时间戳与时间之间转化
    select unix_timestamp() as 获得unix时间戳;                   -- 1573525853
    select from_unixtime(unix_timestamp()) as 从时间戳获得时间;  -- 2019-11-12 10:30:53
    
    
    # 返回日期/时间的单独部分（年、月、日等）
    select EXTRACT(YEAR  FROM now()) as 返回年份;                -- 2019
    select EXTRACT(MONTH FROM now()) as 返回月份;                -- 11
    select EXTRACT(DAY   FROM now()) as 返回天数;                -- 12
    select year(now())  as 返回年份2;                            -- 2019
    select month(now()) as 返回月份2;                            -- 11
    select day(now())   as 返回天数2;                            -- 12
    
    
    # 对日期进行格式化
    select DATE_FORMAT(now(), '%Y%m%d') as 格式化时间1;          -- 20191112
    select DATE_FORMAT(now(), '%Y%m01') as 格式化时间2;          -- 20191101
    select DATE_FORMAT(now(), '%Y-%m')  as 格式化时间3;          -- 2019-11
    
    
    # 时间相加减
    select DATE_SUB(now(),interval 1 day) as 日期相减1;          -- 2019-11-11 10:32:22
    select DATE_ADD(now(),interval -1 day) as 日期相减2;         -- 2019-11-11 10:32:22
    
    select DATE_ADD(now(),interval 1 day) as 日期相加1;          -- 2019-11-13 10:32:22
    select DATE_SUB(now(),interval -1 day) as 日期相加2;         -- 2019-11-13 10:32:22
    
    
    # 前面减后面（只计算天数）
    select DATEDIFF(DATE_ADD(now(),interval 1 day),now()) as 前面日期减后面日期得到天数;
    
    # 后面减去前面
    SELECT TIMESTAMPDIFF(YEAR, '2018-11-13 00:00:00',now()) as 相差年份数;    -- 当前日期2019-11-12：如果2019-11-12 00:00:00结果1；2019-11-11 00:00:00结果1；2019-11-13 00:00:00结果0
    SELECT TIMESTAMPDIFF(MONTH,'2019-03-13 00:00:00',now()) as 相差月份数;    -- 当前日期2019-11-12：如果2019-03-12 00:00:00结果8；2019-03-11 00:00:00结果8；2019-03-13 00:00:00结果7
    SELECT TIMESTAMPDIFF(DAY,  '2019-11-11 11:00:00',now()) as 相差天数;      -- 当前日期2019-11-12 11:00:00 ：如果2019-11-11 11:00:00结果1；2019-11-11 10:00:00结果1；2019-11-11 12:00:00结果0