mysql 時間相關的函式 以及日期和字串互轉

mysql 時間相關的函式

1當前日期的表達

NOW() == CURRENT_TIMESTAMP（） == LOCALTIME（）

CURDATE()

CURTIME()

NOW()	CURDATE()	CURTIME()
2008-12-29 16:25:46	2008-12-29	16:25:46

2選取日期時間的各個部分

2-1 format函式

select DATE_FORMAT(NOW(),"%Y-%m-%d %H:%i");  返回當前時間，%+()  自由組合

格式	描述
%a	縮寫星期名
%b	縮寫月名
%c	月，數值
%D	帶有英文字首的月中的天
%d	月的天，數值(00-31)
%e	月的天，數值(0-31)
%f	微秒
%H	小時 (00-23)
%h	小時 (01-12)
%I	小時 (01-12)
%i	分鐘，數值(00-59)
%j	年的天 (001-366)
%k	小時 (0-23)
%l	小時 (1-12)
%M	月名
%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 位

2-2 時間函式選取（year、month……）

SELECT now(),date(now()); -- 日期

SELECT now(),time(now()); -- 時間

SELECT now(),year(now()); -- 年

SELECT now(),quarter(now()); -- 季度

SELECT now(),month(now()); -- 月

SELECT now(),week(now()); -- 周

SELECT now(),day(now()); -- 日

SELECT now(),hour(now()); -- 小時

SELECT now(),minute(now()); -- 分鐘

SELECT now(),second(now()); -- 秒

SELECT now(),microsecond(now()); -- 微秒

2-3 extract函式

EXTRACT(unit FROM date)：從日期中抽取出某個單獨的部分或組合

SELECT now(),extract(YEAR FROM now()); -- 年

SELECT now(),extract(QUARTER FROM now()); -- 季度

SELECT now(),extract(MONTH FROM now()); -- 月

SELECT now(),extract(WEEK FROM now()); -- 周

SELECT now(),extract(DAY FROM now()); -- 日

SELECT now(),extract(HOUR FROM now()); -- 小時

SELECT now(),extract(MINUTE FROM now()); -- 分鐘

SELECT now(),extract(SECOND FROM now()); -- 秒

SELECT now(),extract(YEAR_MONTH FROM now()); -- 年月

SELECT now(),extract(HOUR_MINUTE FROM now()); -- 時分

3 Date/Datetime轉為String

使用函式 DATE_FORMAT（date,format）

date:需要轉換的日期 format:格式化的樣式

SELECT DATE_FORMAT((SELECT creation_time FROM assess_template where id =26 ),"%Y");  
返回結果是 2020; 可根據需要調整為 年月/ 年月日 ……

4 String 轉為Date/Datetime

使用的函式：STR_TO_DATE(str,format)

str:字元形式的日期 format:格式化樣式

SELECT STR_TO_DATE((SELECT consume_start_time from consume_bill WHERE id=1),"%y"); 
返回結果是 2020-00-00  因為只能轉換為DATE/DATETIME格式，結果只能是XXXX-XX-XX/XXXX-XX-XX XX:XX

5個性化顯示時間日期

dayofweek(date)

dayofmonth(date)

dayofyear(date)

##分別返回日期在一週、一月、一年中是第幾天

相似的 還有 week（date） month（date） 一年中的第幾周 第幾月

#2020/9/3 星期4
SELECT now(),dayofweek(now());  4

SELECT now(),dayofmonth(now());  30

select now(),dayofyear(now());   274

​ dayname()

monthname()

select dayname(now()),monthname(now()); 返回 Wednesday September

6日期時期運算函式

DATE_ADD（date，INTERVAL expr unit）

DATE_SUB（date，INTERVAL expr unit);

interval是間隔型別關鍵字

expr是一個表示式，對應後面的型別

SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 DAY);    加一天

SELECT date_sub('2005-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND);
結果 2004-12-30 22:58:59  減1天1小時1分1秒

unit/單元	代表含義
HOUR	小時
MINUTE	分
SECOND	秒
MICROSECOND	毫秒
YEAR	年
MONTH	月
DAY	日
WEEK	周
QUARTER	季
YEAR_MONTH	年和月
DAY_HOUR	日和小時
DAY_MINUTE	日和分鐘
DAY_ SECOND	日和秒
HOUR_MINUTE	小時和分
HOUR_SECOND	小時和秒
MINUTE_SECOND	分鐘和秒

7時間差函式

timestampdiff(interval, datetime1,datetime2)

返回（時間2-時間1）的時間差，結果單位由interval引數給出。

SELECT timestampdiff(MONTH, NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR));  //返回12

還有 datediff 和 timediff ，datediff以天為單位， timediff 以小時分秒為單位。