MySQL中datetime、date、time、str之間如何轉(zhuǎn)化

這篇文章主要介紹“MySQL中datetime、date、time、str之間如何轉(zhuǎn)化”的相關(guān)知識，小編通過實(shí)際案例向大家展示操作過程，操作方法簡單快捷，實(shí)用性強(qiáng)，希望這篇“MySQL中datetime、date、time、str之間如何轉(zhuǎn)化”文章能幫助大家解決問題。

創(chuàng)新互聯(lián)專注于網(wǎng)站建設(shè)，為客戶提供網(wǎng)站建設(shè)、成都網(wǎng)站制作、網(wǎng)頁設(shè)計開發(fā)服務(wù)，多年建網(wǎng)站服務(wù)經(jīng)驗(yàn)，各類網(wǎng)站都可以開發(fā)，高端網(wǎng)站設(shè)計，公司官網(wǎng)，公司展示網(wǎng)站，網(wǎng)站設(shè)計，建網(wǎng)站費(fèi)用，建網(wǎng)站多少錢，價格優(yōu)惠，收費(fèi)合理。

datetime、date、time、str之間的轉(zhuǎn)化與比較

SELECT NOW(),CURDATE(),CURTIME(),
  -- datetime 轉(zhuǎn) date time
  NOW(), DATE(NOW()), TIME (NOW()), CONCAT(DATE(NOW()), ' ', TIME (NOW())),
  -- str 轉(zhuǎn) 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());
 
-- 獲取當(dāng)前時間的函數(shù)
select CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME;	-- 當(dāng)前時間
select CURRENT_TIMESTAMP(), CURRENT_DATE(), CURRENT_TIME(), CURDATE(), CURTIME();	-- 當(dāng)前時間
select now(), sysdate(), SLEEP(3), now(), sysdate();	-- 當(dāng)前時間
select UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME();	-- UTC時間
select UNIX_TIMESTAMP(), UNIX_TIMESTAMP(now());-- 函數(shù)返回連接當(dāng)前時區(qū)內(nèi)的值（時間戳）
select FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y-%m-%d %H:%i:%s'); -- 時間戳格式化
select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s'); -- 時間格式化

-- ----------------- 補(bǔ)充日期操作
/* 對日期進(jìn)行 加減（兩個函數(shù)記一個即可）
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);
-- 復(fù)合型：加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:01');

MySQL日期和時間數(shù)據(jù)類型(DATE、TIME、 DATETIME、 TIMESTAMP和YEAR

本文基于MySQL 5.7版本。

MySQL中表示時間值的日期和時間數(shù)據(jù)類型有以下五個：

（1）DATE

（2）TIME

（3）DATETIME

（4）TIMESTAMP

（5）YEAR

日期和時間數(shù)據(jù)類型語法

MySQL允許對TIME、DATETIME和TIMESTAMP使用小數(shù)秒，精度最高可達(dá)微秒（6位小數(shù)）。要定義包含小數(shù)秒日期和時間數(shù)據(jù)類型的列，使用

type_name(fsp)，

其中type_name是TIME、DATETIME或TIMESTAMP，而fsp是小數(shù)秒精度，fsp的值，如果給定，必須在0 ~ 6之間。值為0表示沒有小數(shù)部分。如果省略，默認(rèn)精度為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類型，但允許使用字符串或數(shù)字將值賦給DATE列。字面常量轉(zhuǎn)換規(guī)則如下：

（1）格式為'YYYY-MM-DD'或'YY-MM-DD'的字符串。任何標(biāo)點(diǎn)字符都可以用作日期部分之間的分隔符。例如，'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格式的數(shù)字，只要該數(shù)字作為日期有意義。例如，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類型。但允許使用字符串或數(shù)字向DATETIME列賦值。字面常量轉(zhuǎn)換規(guī)則如下：

表中的任何TIMESTAMP或DATETIME列都可以具有自動初始化和更新屬性。

（1）格式為“YYYY-MM-DD hh:mm:ss”或“YY-MM-DD hh:mm:ss”字符串。任何標(biāo)點(diǎn)字符都可以用作日期部分或時間部分之間的分隔符。例如，'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）在日期和時間部分和小數(shù)秒部分之間識別的唯一分隔符是小數(shù)點(diǎn)。

（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格式的數(shù)字，只要該數(shù)字作為日期有意義。例如，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指的是世界標(biāo)準(zhǔn)時間），TIMESTAMP值存儲為自紀(jì)元('1970-01-01 00:00:00' UTC)以來的秒數(shù)，但是它不能表示'1970-01-01 00:00:00'，因?yàn)檫@表示從紀(jì)元開始的0秒，值0保留用于表示“0000-00-00 00:00:00”，即“零”的TIMESTAMP值。TIMESTAMP的字面常量同DATETIME。

上面date_time_test表ts列是TIMESTAMP，一旦創(chuàng)建后，我們發(fā)現(xiàn)ts列默認(rèn)值為當(dāng)前TIMESTAMP，而且可自動更新為當(dāng)前時間戳，如圖：

支持的范圍'1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC（UTC指的是世界標(biāo)準(zhǔn)時間），TIMESTAMP值存儲為自紀(jì)元('1970-01-01 00:00:00' UTC)以來的秒數(shù)，但是它不能表示'1970-01-01 00:00:00'，因?yàn)檫@表示從紀(jì)元開始的0秒，值0保留用于表示“0000-00-00 00:00:00”，即“零”的TIMESTAMP值。TIMESTAMP的字面常量同DATETIME。

上面date_time_test表ts列是TIMESTAMP，一旦創(chuàng)建后，我們發(fā)現(xiàn)ts列默認(rèn)值為當(dāng)前TIMESTAMP，而且可自動更新為當(dāng)前時間戳，如圖：

向date_time_test表中插入一條數(shù)據(jù)，如下：

insert date_time_test(t) values('00:00:01');

登錄后復(fù)制

查看數(shù)據(jù)，如圖：

發(fā)現(xiàn)ts列自動更新為插入數(shù)據(jù)時的時間戳。

更新當(dāng)前數(shù)據(jù)，如圖：

update date_time_test set t='00:00:02' where t='00:00:01';

登錄后復(fù)制

再次查看時間戳，如圖：

ts列已經(jīng)更新為當(dāng)前時間戳。這和MySQL的系統(tǒng)變量explicit_defaults_for_timestamp有關(guān)，

show variables like 'explicit_defaults_for_timestamp';

登錄后復(fù)制

如圖：

explicit_defaults_for_timestamp處于關(guān)閉狀態(tài)，此時，插入數(shù)據(jù)時，如果ts沒有指定值，會被初始化為默認(rèn)值，更新數(shù)據(jù)時，ts列同時更新。 explicit_defaults_for_timestamp處于打開狀態(tài)，不會被初始化為默認(rèn)，也不具備自動更新為當(dāng)前時間戳。也可以對TIMESTAMP和DATETIME如下顯示定義默認(rèn)值和自動更新：

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，但允許使用字符串或數(shù)字向TIME列賦值。TIME類型這個時間范圍表明它不僅可以表示一天的時間，也可以表示經(jīng)過的時間或兩個事件之間的時間間隔。字面常量轉(zhuǎn)換規(guī)則如下：

（1）MySQL將帶有冒號的TIME值縮寫為一天中的時間，例如：'11:12'表示'11:12:00'，而不是 '00:11:12'；

（2）MySQL將沒有冒號的TIME縮寫值最右邊的兩個數(shù)字看成秒，例如：'1112'和1112都表示'00:11:12'

（3）在時間部分和小數(shù)秒部分之間識別的唯一分隔符是小數(shù)點(diǎn)；

（4）默認(rèn)情況下，位于TIME范圍之外但在其他方面有效的值將被裁剪到該范圍中最近的端點(diǎn)。例如，'-850:00:00'和'850:00:00'會轉(zhuǎn)換為'-838:59:59'和'838:59:59'。無效的TIME值將被轉(zhuǎn)換為“00:00:00”。注意，由于'00:00:00'本身是一個有效的TIME值，因此無法從存儲在表中的'00:00:00'值判斷原始值是'00:00:00'還是無效的。

YEAR和YEAR(4)

支持的范圍1901到2155，或者0000。MySQL以YYYY格式顯示YEAR。但允許使用字符串或數(shù)字向YEAR列賦值。

SUM()和AVG()聚合函數(shù)不能處理時間值。要解決這個問題，請將其轉(zhuǎn)換為數(shù)字，然后執(zhí)行聚合操作，最后將其轉(zhuǎn)換回時間值。例子:

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位數(shù)字1901 到 2155；

（3）1或者2位字符串'0' 到 '99'，MySQL將'0'到'69'轉(zhuǎn)化為2000到2069，將'70'到'99'轉(zhuǎn)化為1970到1999；

（4）1或者2位數(shù)字0到 99，MySQL將1到69轉(zhuǎn)換為YEAR值2001到2069，將70到99轉(zhuǎn)化為YEAR值1970到1999，0轉(zhuǎn)化為YEAR值0000。

注：

如果沒有啟用strict SQL模式，MySQL會將無效的YEAR值轉(zhuǎn)換為0000。在嚴(yán)格SQL模式下，試圖插入無效的YEAR值將產(chǎn)生錯誤。

總結(jié)如下：

	DATE	TIME	DATETIME	TIMESTAMP	YEAR
取值范圍	'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
小數(shù)位精度	0	0~6	0~6	0~6	0
顯示格式	YYYY-MM-DD	hh:mm:ss[.fraction]	YYYY-MM-DD hh:mm:ss[.fraction]	YYYY-MM-DD hh:mm:ss[.fraction]	YYYY
賦值	允許使用符合格式的字符串或數(shù)字賦值	允許使用符合格式的字符串或數(shù)字賦值	允許使用符合格式的字符串或數(shù)字賦值	允許使用符合格式的字符串或數(shù)字賦值	允許使用符合格式的字符串或數(shù)字賦值
小數(shù)位分隔符	無	只識別小數(shù)點(diǎn).	只識別小數(shù)點(diǎn).	只識別小數(shù)點(diǎn).	無
日期分隔符	任何標(biāo)點(diǎn)	無	任何標(biāo)點(diǎn)	任何標(biāo)點(diǎn)	無
時間分隔符	無	任何標(biāo)點(diǎn)	任何標(biāo)點(diǎn)	任何標(biāo)點(diǎn)	無
無效值處理（strict mode關(guān)閉狀態(tài)）	轉(zhuǎn)化為 0000-00-00	轉(zhuǎn)化為 0000-00-00 00:00:00	轉(zhuǎn)化為 0000-00-00 00:00:00	轉(zhuǎn)換為 00:00:00	轉(zhuǎn)換為0000

關(guān)于“MySQL中datetime、date、time、str之間如何轉(zhuǎn)化”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識，可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，小編每天都會為大家更新不同的知識點(diǎn)。

本文標(biāo)題：MySQL中datetime、date、time、str之間如何轉(zhuǎn)化
路徑分享：http://bm7419.com/article10/geiggo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供ChatGPT、網(wǎng)站維護(hù)、云服務(wù)器、網(wǎng)站導(dǎo)航、微信公眾號、域名注冊

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)