共計 6331 個字符，預計需要花費 16 分鐘才能閱讀完成。
這篇文章主要介紹“MySQL 中 datetime、date、time、str 之間如何轉化”的相關知識，丸趣 TV 小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“MySQL 中 datetime、date、time、str 之間如何轉化”文章能幫助大家解決問題。
datetime、date、time、str 之間的轉化與比較
SELECT NOW(),CURDATE(),CURTIME(),
-- datetime 轉 date time
NOW(), DATE(NOW()), TIME (NOW()), CONCAT(DATE(NOW()), , TIME (NOW())),
-- str 轉 datetime date time
str_to_date(2019-04-25 08:50:00 , %Y-%m-%d %H:%i:%s),DATE(2019-04-25 08:50:00),TIME (2019-04-25 08:50:00),
str_to_date(2019-04-25 08:50:00 , %Y-%m-%d %T),
-- 比較
NOW() = CONCAT(DATE(NOW()), , TIME (NOW())),
DATE(NOW()) = 2019-04-25 ,
NOW() = DATE(NOW()),
NOW() = TIME (NOW());
-- 獲取當前時間的函數
select CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME; -- 當前時間
select CURRENT_TIMESTAMP(), CURRENT_DATE(), CURRENT_TIME(), CURDATE(), CURTIME(); -- 當前時間
select now(), sysdate(), SLEEP(3), now(), sysdate(); -- 當前時間
select UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME(); -- UTC 時間
select UNIX_TIMESTAMP(), UNIX_TIMESTAMP(now());-- 函數返回連接當前時區內的值（時間戳）select FROM_UNIXTIME(UNIX_TIMESTAMP(), %Y-%m-%d %H:%i:%s -- 時間戳格式化
select DATE_FORMAT(now(), %Y-%m-%d %H:%i:%s -- 時間格式化
-- ----------------- 補充日期操作
/* 對日期進行 加減（兩個函數記一個即可）DATE_ADD(date,INTERVAL expr type);
DATE_SUB(date,INTERVAL expr type);
type 取值：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、YEAR_MONTH、DAY_SECOND 等等
-- 加 1 天
select DATE_ADD(now(),INTERVAL 1 DAY), DATE_SUB(now(),INTERVAL -1 DAY);
-- 復合型：加 1 年 1 月
select DATE_ADD(now(),INTERVAL 1 1 YEAR_MONTH), DATE_SUB(now(),INTERVAL -1 -1 YEAR_MONTH);
-- 加 1 天 2 時 3 分 4 秒
select DATE_ADD(2020-12-23 00:00:00 ,INTERVAL 1 2:3:4 DAY_SECOND),DATE_SUB(2020-12-23 00:00:00 ,INTERVAL -1 -2:-3:-4 DAY_SECOND);
/* 獲取兩個日期之間的差值（datetime_expr2-datetime_expr1 的值）TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
unit：差值的單位，取值：FRAC_SECOND（低版本不支持）、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER 或 YEAR
-- 查詢兩個日期間的差值（單位秒）select TIMESTAMPDIFF(SECOND, 2020-12-01 00:00:00 , 2020-12-01 01:01:01MySQL 日期和時間數據類型(DATE、TIME、DATETIME、TIMESTAMP 和 YEAR
本文基于 MySQL 5.7 版本。
MySQL 中表示時間值的日期和時間數據類型有以下五個：
（1）DATE
（2）TIME
（3）DATETIME
（4）TIMESTAMP
（5）YEAR
日期和時間數據類型語法
MySQL 允許對 TIME、DATETIME 和 TIMESTAMP 使用小數秒，精度最高可達微秒（6 位小數）。要定義包含小數秒日期和時間數據類型的列，使用
type_name(fsp)，
其中 type_name 是 TIME、DATETIME 或 TIMESTAMP，而 fsp 是小數秒精度，fsp 的值，如果給定，必須在 0 ~ 6 之間。值為 0 表示沒有小數部分。如果省略，默認精度為 0。
例如:
CREATE TABLE date_time_test (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));Date
支持的范圍 1000-01-01 to 9999-12-31，MySQL 以 YYYY-MM-DD 格式顯示 Date 類型，但允許使用字符串或數字將值賦給 DATE 列。字面常量轉換規則如下：
（1）格式為 YYYY-MM-DD 或 YY-MM-DD 的字符串。任何標點字符都可以用作日期部分之間的分隔符。例如，2012-12-31、2012/12/31、2012^12^31 和 2012@12@31 是等價的。
（2）格式為 YYYYMMDD 或 YYMMDD 的沒有分隔符的字符串，只要該字符串具有日期的意義。例如，20070523 和 070523 被解釋為 2007-05-23，但 071332 是非法的(它有無意義的月和日部分)，會變成 0000-00-00。
（3）作為 YYYYMMDD 或 YYMMDD 格式的數字，只要該數字作為日期有意義。例如，19830905 和 830905 被解釋為“1983-09-05”。
DATETIME 和 DATETIME(fsp)
DATETIME 類型是 Date 和 Time 的組合。支持的范圍從 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999。MySQL 以 YYYY-MM-DD hh:mm:ss[.fraction] 的格式顯示 DATETIME 類型。但允許使用字符串或數字向 DATETIME 列賦值。字面常量轉換規則如下：
表中的任何 TIMESTAMP 或 DATETIME 列都可以具有自動初始化和更新屬性。
（1）格式為“YYYY-MM-DD hh:mm:ss”或“YY-MM-DD hh:mm:ss”字符串。任何標點字符都可以用作日期部分或時間部分之間的分隔符。例如，2012-12-31 11:30:45、2012^12^31 11+30+45、2012/12/31 11*30*45 和 2012@12@31 11^30^45 是等價的。
（2）在日期和時間部分和小數秒部分之間識別的唯一分隔符是小數點。
（3）日期和時間部分可以用 T 而不是空格分隔。例如，2012-12-31 11:30:45 2012-12-31 t11:30:45 是等價的。
（4）格式為 YYYYMMDDhhmmss 或 YYMMDDhhmmss 無分隔符的字符串，只要該字符串具有日期的意義。例如，20070523091528 和 070523091528 被解釋為 2007-05-23 09:15:28，但 071122129015 是非法的(它有一個無意義的分鐘部分)，會變成 0000-00-00 00:00:00。
（5）YYYYMMDDhhmmss 或 YYMMDDhhmmss 格式的數字，只要該數字作為日期有意義。例如，19830905132800 和 830905132800 被解釋為“1983-09-05 13:28:00”。
TIMESTAMP 和 TIMESTAMP[(fsp)]
支持的范圍 1970-01-01 00:00:01.000000 UTC to 2038-01-19 03:14:07.999999 UTC（UTC 指的是世界標準時間），TIMESTAMP 值存儲為自紀元 (1970-01-01 00:00:00 UTC) 以來的秒數，但是它不能表示 1970-01-01 00:00:00，因為這表示從紀元開始的 0 秒，值 0 保留用于表示“0000-00-00 00:00:00”，即“零”的 TIMESTAMP 值。TIMESTAMP 的字面常量同 DATETIME。
上面 date_time_test 表 ts 列是 TIMESTAMP，一旦創建后，我們發現 ts 列默認值為當前 TIMESTAMP，而且可自動更新為當前時間戳，如圖：
支持的范圍 1970-01-01 00:00:01.000000 UTC to 2038-01-19 03:14:07.999999 UTC（UTC 指的是世界標準時間），TIMESTAMP 值存儲為自紀元 (1970-01-01 00:00:00 UTC) 以來的秒數，但是它不能表示 1970-01-01 00:00:00，因為這表示從紀元開始的 0 秒，值 0 保留用于表示“0000-00-00 00:00:00”，即“零”的 TIMESTAMP 值。TIMESTAMP 的字面常量同 DATETIME。
上面 date_time_test 表 ts 列是 TIMESTAMP，一旦創建后，我們發現 ts 列默認值為當前 TIMESTAMP，而且可自動更新為當前時間戳，如圖：
向 date_time_test 表中插入一條數據，如下：
insert date_time_test(t) values(00:00:01登錄后復制
查看數據，如圖：
發現 ts 列自動更新為插入數據時的時間戳。
更新當前數據，如圖：
update date_time_test set t= 00:00:02 where t= 00:00:01登錄后復制
再次查看時間戳，如圖：
ts 列已經更新為當前時間戳。這和 MySQL 的系統變量 explicit_defaults_for_timestamp 有關，
show variables like explicit_defaults_for_timestamp登錄后復制
如圖：
explicit_defaults_for_timestamp 處于關閉狀態，此時，插入數據時，如果 ts 沒有指定值，會被初始化為默認值，更新數據時，ts 列同時更新。explicit_defaults_for_timestamp 處于打開狀態，不會被初始化為默認，也不具備自動更新為當前時間戳。也可以對 TIMESTAMP 和 DATETIME 如下顯示定義默認值和自動更新：
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);TIME 和 TIME(fsp)
支持的范圍 -838:59:59.000000 to 838:59:59.000000，MySQL 以 hh:mm:ss[.fraction] 格式顯示 TIME，但允許使用字符串或數字向 TIME 列賦值。TIME 類型這個時間范圍表明它不僅可以表示一天的時間，也可以表示經過的時間或兩個事件之間的時間間隔。字面常量轉換規則如下：
（1）MySQL 將帶有冒號的 TIME 值縮寫為一天中的時間，例如：11:12 表示 11:12:00，而不是 00:11:12；
（2）MySQL 將沒有冒號的 TIME 縮寫值最右邊的兩個數字看成秒，例如：1112 和 1112 都表示 00:11:12
（3）在時間部分和小數秒部分之間識別的唯一分隔符是小數點；
（4）默認情況下，位于 TIME 范圍之外但在其他方面有效的值將被裁剪到該范圍中最近的端點。例如，-850:00:00 和 850:00:00 會轉換為 -838:59:59 和 838:59:59。無效的 TIME 值將被轉換為“00:00:00”。注意，由于 00:00:00 本身是一個有效的 TIME 值，因此無法從存儲在表中的 00:00:00 值判斷原始值是 00:00:00 還是無效的。
YEAR 和 YEAR(4)
支持的范圍 1901 到 2155，或者 0000。MySQL 以 YYYY 格式顯示 YEAR。但允許使用字符串或數字向 YEAR 列賦值。
SUM()和 AVG()聚合函數不能處理時間值。要解決這個問題，請將其轉換為數字，然后執行聚合操作，最后將其轉換回時間值。例子:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;YEAR 接受如下格式的輸入：
（1）4 位字符串 1901 到 2155；
（2）4 位數字 1901 到 2155；
（3）1 或者 2 位字符串 0 到 99，MySQL 將 0 到 69 轉化為 2000 到 2069，將 70 到 99 轉化為 1970 到 1999；
（4）1 或者 2 位數字 0 到 99，MySQL 將 1 到 69 轉換為 YEAR 值 2001 到 2069，將 70 到 99 轉化為 YEAR 值 1970 到 1999，0 轉化為 YEAR 值 0000。
注：
如果沒有啟用 strict SQL 模式，MySQL 會將無效的 YEAR 值轉換為 0000。在嚴格 SQL 模式下，試圖插入無效的 YEAR 值將產生錯誤。
總結如下：
DATETIMEDATETIMETIMESTAMPYEAR 取值范圍
1000-01-01
到
9999-12-31
-838:59:59.000000
到
838:59:59.000000
1000-01-01 00:00:00.000000 到
9999-12-31 23:59:59.999999
1970-01-01 00:00:01.000000 UTC to
2038-01-19 03:14:07.999999 UTC
1901
到
2155
或者 0000
小數位精度 00~60~60~60 顯示格式 YYYY-MM-DDhh:mm:ss[.fraction]YYYY-MM-DD hh:mm:ss[.fraction]YYYY-MM-DD hh:mm:ss[.fraction]YYYY 賦值允許使用符合格式的字符串或數字賦值允許使用符合格式的字符串或數字賦值允許使用符合格式的字符串或數字賦值允許使用符合格式的字符串或數字賦值允許使用符合格式的字符串或數字賦值小數位分隔符無只識別小數點. 只識別小數點. 只識別小數點. 無日期分隔符任何標點無任何標點任何標點無時間分隔符無任何標點任何標點任何標點無無效值處理（strict mode 關閉狀態）
轉化為
0000-00-00
轉化為
0000-00-00 00:00:00
轉化為
0000-00-00 00:00:00
轉換為
00:00:00
轉換為 0000
關于“MySQL 中 datetime、date、time、str 之間如何轉化”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識，可以關注丸趣 TV 行業資訊頻道，丸趣 TV 小編每天都會為大家更新不同的知識點。
