mysql之TIMESTAMP(時光戳)用法詳解。本站提示廣大學習愛好者:(mysql之TIMESTAMP(時光戳)用法詳解)文章只能為提供參考,不一定能成為您想要的結果。以下是mysql之TIMESTAMP(時光戳)用法詳解正文
1、TIMESTAMP的變體
TIMESTAMP時光戳在創立的時刻可以有多重分歧的特征,如:
1.在創立新記載和修正現有記載的時刻都對這個數據列刷新:
TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
2.在創立新記載的時刻把這個字段設置為以後時光,但今後修正時,不再刷新它:
TIMESTAMP DEFAULT CURRENT_TIMESTAMP
3.在創立新記載的時刻把這個字段設置為0,今後修正時刷新它:
TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
4.在創立新記載的時刻把這個字段設置為給定值,今後修正時刷新它:
TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP
MySQL今朝不支撐列的Default 為函數的情勢,如到達你某列的默許值為以後更新日期與時光的功效,你可使用TIMESTAMP列類型,上面就具體解釋TIMESTAMP列類型
2、TIMESTAMP列類型
TIMESTAMP值可以從1970的某時的開端一向到2037年,精度為一秒,其值作為數字顯示。
TIMESTAMP值顯示尺寸的格局以下表所示:
:
+---------------+----------------+ | 列類型 | 顯示格局 | | TIMESTAMP(14) | YYYYMMDDHHMMSS | | TIMESTAMP(12) | YYMMDDHHMMSS | | TIMESTAMP(10) | YYMMDDHHMM | | TIMESTAMP(8) | YYYYMMDD | | TIMESTAMP(6) | YYMMDD | | TIMESTAMP(4) | YYMM | | TIMESTAMP(2) | YY | +---------------+----------------+
“完全”TIMESTAMP格局是14位,但TIMESTAMP列也能夠用更短的顯示尺寸,發明最多見的顯示尺寸是6、8、12、和14。
你可以在創立表時指定一個隨意率性的顯示尺寸,然則界說列長為0或比14年夜均會被強迫界說為列長14。
列長在從1~13規模的奇數值尺寸均被強迫為下一個更年夜的偶數。
列如:
界說字段長度 強迫字段長度 TIMESTAMP(0) -> TIMESTAMP(14) TIMESTAMP(15)-> TIMESTAMP(14) TIMESTAMP(1) -> TIMESTAMP(2) TIMESTAMP(5) -> TIMESTAMP(6)
一切的TIMESTAMP列都有異樣的存儲年夜小,應用被指定的時代時光值的完全精度(14位)存儲正當的值不斟酌顯示尺寸。不正當的日期,將會被強迫為0存儲
這有幾個含義:
1.固然你建表時界說了列TIMESTAMP(8),但在你停止數據拔出與更新時TIMESTAMP列現實上保留了14位的數據(包含年代日時分秒),只不外在你停止查詢時MySQL前往給你的是8位的年代日數據。假如你應用ALTER TABLE拓寬一個狹小的TIMESTAMP列,之前被“隱藏”的信息將被顯示。
2.異樣,減少一個TIMESTAMP列不會招致信息掉去,除感到上值在顯示時,較少的信息被顯示出。
3.雖然TIMESTAMP值被存儲為完全精度,直接操作存儲值的獨一函數是UNIX_TIMESTAMP();因為MySQL前往TIMESTAMP列的列值是進過格局化後的檢索的值,這意味著你能夠不克不及應用某些函數來操作TIMESTAMP列(例如HOUR()或SECOND()),除非TIMESTAMP值的相干部門被包括在格局化的值中。
例如,一個TIMESTAMP列只要被界說為TIMESTAMP(10)以上時,TIMESTAMP列的HH部門才會被顯示,是以在更短的TIMESTAMP值上應用HOUR()會發生一個弗成預知的成果。
4.不正當TIMESTAMP值被變換到恰當類型的“零”值(00000000000000)。(DATETIME,DATE亦然)
例如你可使用以下語句來驗證:
CREATE TABLE test ('id' INT (3) UNSIGNED AUTO_INCREMENT, 'date1' TIMESTAMP (8) PRIMARY KEY('id')); INSERT INTO test SET id = 1; SELECT * FROM test; +----+----------------+ | id | date1 | +----+----------------+ | 1 | 20021114 | +----+----------------+ ALTER TABLE test CHANGE 'date1' 'date1' TIMESTAMP(14); SELECT * FROM test; +----+----------------+ | id | date1 | +----+----------------+ | 1 | 20021114093723 | +----+----------------+
你可使用TIMESTAMP列類型主動地用以後的日期和時光標志INSERT或UPDATE的操作。
假如你有多個TIMESTAMP列,只要第一個主動更新。主動更新第一個TIMESTAMP列鄙人列任何前提下產生:
1.列值沒有明白地在一個INSERT或LOAD DATA INFILE語句中指定。
2.列值沒有明白地在一個UPDATE語句中指定且別的一些的列轉變值。(留意一個UPDATE設置一個列為它曾經有的值,這將不惹起TIMESTAMP列被更新,由於假如你設置一個列為它以後的值,MySQL為了效力而疏忽更改。)
3.你明白地設定TIMESTAMP列為NULL.
4.除第一個之外的TIMESTAMP列也能夠設置到以後的日期和時光,只需將列設為NULL,或NOW()。
CREATE TABLE test ( 'id' INT (3) UNSIGNED AUTO_INCREMENT, 'date1' TIMESTAMP (14), 'date2' TIMESTAMP (14), PRIMARY KEY('id') ); INSERT INTO test (id, date1, date2) VALUES (1, NULL, NULL); INSERT INTO test SET id= 2; +----+----------------+----------------+ | id | date1 | date2 | +----+----------------+----------------+ | 1 | 20021114093723 | 20021114093723 | | 2 | 20021114093724 | 00000000000000 | +----+----------------+----------------+
第一條指令因設date1、date2為NULL,所以date1、date2值均為以後時光第二條指令因沒有設date1、date2列值,第一個TIMESTAMP列date1為更新為以後時光,而二個TIMESTAMP列date2因日期不正當而變成“00000000000000”
UPDATE test SET id= 3 WHERE id=1; +----+----------------+----------------+ | id | date1 | date2 | +----+----------------+----------------+ | 3 | 20021114094009 | 20021114093723 | | 2 | 20021114093724 | 00000000000000 | +----+----------------+----------------+
這條指令沒有明白地設定date2的列值,所以第一個TIMESTAMP列date1將被更新為以後時光
UPDATE test SET id= 1,date1=date1,date2=NOW() WHERE id=3; +----+----------------+----------------+ | id | date1 | date2 | +----+----------------+----------------+ | 1 | 20021114094009 | 20021114094320 | | 2 | 20021114093724 | 00000000000000 | +----+----------------+----------------+
這條指令因設定date1=date1,所以在更新數據時date1列值其實不會產生轉變而因設定date2=NOW(),所以在更新數據時date2列值會被更新為以後時光此指令等效為:
UPDATE test SET id= 1,date1=date1,date2=NULL WHERE id=3;
因MySQL前往的 TIMESTAMP 列為數字顯示情勢,你可以用DATE_FROMAT()函數來格局化 TIMESTAMP 列,以下所示:
SELECT id,DATE_FORMAT(date1,'%Y-%m-%d %H:%i:%s') As date1, DATE_FORMAT(date2,'%Y-%m-%d %H:%i:%s') As date2 FROM test; +----+---------------------+---------------------+ | id | date1 | date2 | +----+---------------------+---------------------+ | 1 | 2002-11-14 09:40:09 | 2002-11-14 09:43:20 | | 2 | 2002-11-14 09:37:24 | 0000-00-00 00:00:00 | +----+---------------------+---------------------+ SELECT id,DATE_FORMAT(date1,'%Y-%m-%d') As date1, DATE_FORMAT(date2,'%Y-%m-%d') As date2 FROM test; +----+-------------+-------------+ | id | date1 | date2 | +----+-------------+-------------+ | 1 | 2002-11-14 | 2002-11-14 | | 2 | 2002-11-14 | 0000-00-00 | +----+-------------+-------------+
在某種水平上,你可以把一種日期類型的值賦給一個分歧的日期類型的對象。
但是,而特別留意的是:值有能夠產生一些轉變或信息的喪失:
1.假如你將一個DATE值賦給一個DATETIME或TIMESTAMP對象,成果值的時光部門被設置為'00:00:00',由於DATE值中不包括有時光信息。
2.假如你將一個DATETIME或TIMESTAMP值賦給一個DATE對象,成果值的時光部門被刪除,由於DATE類型不存儲時光信息。
3.雖然DATETIME, DATE和TIMESTAMP值全都可以用異樣的格局集來指定,但一切類型不都有異樣的值規模。
例如,TIMESTAMP值不克不及比1970早,也不克不及比2037晚,這意味著,一個日期例如'1968-01-01',看成為一個DATETIME或DATE值時它是正當的,但它不是一個准確TIMESTAMP值!而且假如將如許的一個對象賦值給TIMESTAMP列,它將被變換為0。
3、當指定日期值時,小心某些缺點:
1.許可作為字符串指定值的寬松格局能被誘騙。例如,由於“:”分隔符的應用,值'10:11:12'能夠看起來像時光值,然則假如在一個日期中應用,高低文將作為年份被說明成'2010-11-12'。值'10:45:15'將被變換到'0000-00-00',由於'45'不是一個正當的月份。
2.以2位數字指定的年值是隱約的,由於世紀是未知的。MySQL應用以下規矩說明2位年值:
在00-69規模的年值被變換到2000-2069。 在規模70-99的年值被變換到1970-1999。
PS:這裡再為年夜家推舉一個本站Unix時光戳轉換對象,附帶了各類說話下時光戳的操作辦法,包含PHP、MySQL、SQL Server、java等的時光戳獲得與轉換操作技能:
Unix時光戳(timestamp)轉換對象:http://tools.jb51.net/code/unixtime