mysql日期函式總結

文中程式碼未特殊說明時使用的mysql版本為5.7
文件地址：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全部符號

說明符	說明
%a	工作日的縮寫名稱 (Sun…Sat)
%b	月份的縮寫名稱 (Jan…Dec)
%c	月份，數字形式(0…12)
%D	帶有英語字尾的該月日期 (0th, 1st, 2nd, 3rd, …)
%d	該月日期, 數字形式 (00…31)
%e	該月日期, 數字形式(0…31)
%f	微秒 (000000…999999)
%H	小時(00…23)
%h	小時(01…12)
%I	小時 (01…12)
%i	分鐘,數字形式 (00…59)
%j	一年中的天數 (001…366)
%k	小時 (0…23)
%l	小時 (1…12)
%M	月份名稱 (January…December)
%m	月份, 數字形式 (00…12)
%p	上午（AM）或下午（ PM）
%r	時間 , 12小時制 (小時hh:分鐘mm:秒數ss 後加 AM或PM)
%S	秒 (00…59)
%s	秒 (00…59)
%T	時間 , 24小時制 (小時hh:分鐘mm:秒數ss)
%U	周 (00…53), 其中週日為每週的第一天
%u	周 (00…53), 其中週一為每週的第一天
%V	周 (01…53), 其中週日為每週的第一天 ; 和 %X同時使用
%v	周 (01…53), 其中週一為每週的第一天 ; 和 %x同時使用
%W	工作日名稱 (週日…週六)
%w	一週中的每日 (0=週日…6=週六)
%X	該周的年份，其中週日為每週的第一天, 數字形式,4位數;和%V同時使用
%x	該周的年份，其中週一為每週的第一天, 數字形式,4位數;和%v同時使用
%Y	年份, 數字形式,4位數
%y	年份, 數字形式 (2位數)
%%	‘%’文字字元

獲取時間

常用now()，在一個單一詢問中，對諸如NOW() 的函式多次訪問總是會得到同樣的結果，這項原則也適用於 CURDATE()、 CURTIME()、 UTC_DATE()、 UTC_TIME()、UTC_TIMESTAMP()，以及所有和它們意義相同的函式。於此相對應的是SYSDATE()。

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

常用獲取時間函式的區別

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