首頁 >資料庫 >mysql教程 >mysql中生日用什麼類型

mysql中生日用什麼類型

青灯夜游
青灯夜游原創
2022-11-11 15:57:268427瀏覽

mysql中生日可用類型:1、DATE類型,可表示的日期格式為“YYYY-MM-DD”,在儲存時需要3個位元組;2、DATETIME類型,可表示的日期格式為“YYYY-MM-DD HH:MM:SS”,儲存時需要8個位元組;3、TIMESTAMP類型,可表示的日期格式為“YYYY-MM-DD HH:MM:SS”,儲存時需要4個位元組。

mysql中生日用什麼類型

本教學操作環境:windows7系統、mysql8版本、Dell G3電腦。

MySQL中適合生日的日期與時間類型

儲存需求3 個位元組8 位元組#4位元組
類型名稱 #日期格式 日期範圍
DATE YYYY-MM-DD 1000- 01-01 ~ 9999-12-3
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
TIMESTAMP YYYY-MM-DD HH:MM:SS 1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC
#######

DATE 類型

DATE 類型用於只需要日期值時,沒有時間部分,在儲存時需要 3 個位元組。日期格式為 'YYYY-MM-DD',其中 YYYY 表示年,MM 表示月,DD 表示日。

在賦值 DATE 類型的欄位時,可以使用字串類型或數字類型的資料插入,只要符合 DATE 的日期格式即可。如下所示:

  • 以'YYYY-MM-DD' 或'YYYYMMDD' 字元中格式表示的日期,取值範圍為'1000-01-01'~'9999- 12-3'。例如,輸入 '2015-12-31' 或 '20151231',插入資料庫的日期為2015-12-31。

  • 以 'YY-MM-DD' 或 'YYMMDD' 字串格式表示日期,在這裡YY表示兩位的年值。 MySQL 解釋兩位年值的規則:'00~69' 範圍的年值轉換為 '2000~2069','70~99' 範圍的年值轉換為 '1970~1999'。例如,輸入 '15-12-31',插入資料庫的日期為 2015-12-31;輸入 '991231',插入資料庫的日期為 1999-12-31。

  • 以 YYMMDD 數字格式表示的日期,與前面相似,00~69 範圍的年值轉換為 2000~2069,80~99 範圍的年值轉換為 1980~1999。例如,輸入 151231,插入資料庫的日期為 2015-12-31,輸入 991231,插入資料庫的日期為 1999-12-31。

  • 使用 CURRENT_DATE 或 NOW(),插入目前系統日期。

提示:MySQL 允許「不嚴格」語法:任何標點符號都可以用作日期部分之間的間隔符號。例如,'98-11-31'、'98.11.31'、'98/11/31'和'98@11@31' 是等價的,這些值也可以正確地插入資料庫。

DATETIME 類型

DATETIME 類型用於需要同時包含日期和時間資訊的值,在儲存時需要 8 個位元組。日期格式為 'YYYY-MM-DD HH:MM:SS',其中 YYYY 表示年,MM 表示月,DD 表示日,HH 表示小時,MM 表示分鐘,SS 表示秒。

在給DATETIME 類型的欄位賦值時,可以使用字串類型或數字類型的資料插入,只要符合DATETIME 的日期格式即可,如下所示:

  • 以'YYYY-MM-DD HH:MM:SS' 或'YYYYMMDDHHMMSS' 字串格式表示的日期,取值範圍為'1000-01-01 00:00:00'~'9999-12-3 23 :59:59'。例如,輸入 '2014-12-31 05:05:05' 或 '20141231050505’,插入資料庫的 DATETIME 值皆為 2014-12-31 05:05:05。

  • 以 'YY-MM-DD HH:MM:SS' 或 'YYMMDDHHMMSS' 字串格式表示的日期,在這裡 YY 表示兩位的年值。與前面相同,'00~79' 範圍的年值轉換為 '2000~2079','80~99' 範圍的年值轉換為 '1980~1999'。例如,輸入'14-12-31 05:05:05',插入資料庫的DATETIME 為2014-12-31 05:05:05;輸入141231050505,插入資料庫的DATETIME 為2014-12-31 05:05:05 。

  • 以 YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 數字格式表示的日期和時間。例如,輸入 20141231050505,插入資料庫的 DATETIME 為 2014-12-31 05:05:05;輸入 140505050505,插入資料庫的 DATETIME 為 2014-12-31 05:05:插入資料庫的 DATETIME 為 2014-12-31 05:05:05。

提示:MySQL 允許「不嚴格」語法:任何標點符號都可用作日期部分或時間部分之間的間隔符號。例如,'98-12-31 11:30:45'、'98.12.31 11 30 35'、'98/12/31 11*30*45' 和'98@12@31 11^30^45' 是等價的,這些值都可以正確地插入資料庫。

TIMESTAMP 類型

TIMESTAMP 的顯示格式與DATETIME 相同,顯示寬度固定在19 個字符,日期格式為YYYY-MM-DD HH:MM :SS,儲存時需要4 個位元組。但 TIMESTAMP 列的值範圍小於 DATETIME 的值範圍,為 '1970-01-01 00:00:01'UTC~'2038-01-19 03:14:07'UTC。在插入資料時,要保證在合法的取值範圍內。

提示:協調世界時(英:Coordinated Universal Time,法:Temps Universel Coordonné)又稱為世界統一時間、世界標準時間、國際協調時間。英文(CUT)和法文(TUC)的縮寫不同,作為妥協,簡稱 UTC。

TIMESTAMP 與DATETIME 除了儲存位元組和支援的範圍不同外,還有一個最大的差異是:

  • DATETIME 在儲存日期資料時,按實際輸入的格式存儲,即輸入什麼就存儲什麼,與時區無關;

  • 而TIMESTAMP 值的存儲是以UTC(世界標準時間)格式保存的,存儲時對目前時區進行轉換,檢索時轉換回目前時區。即查詢時,根據目前時區的不同,顯示的時間值是不同的。

提示:如果為 DATETIME 或 TIMESTAMP 物件指派 DATE 值,則結果值的時間部分設定為 '00:00:00',因此 DATE 值未包含時間資訊。如果為一個 DATE 物件指派一個 DATETIME 或 TIMESTAMP 值,結果值的時間部分會被刪除,因此DATE 值未包含時間資訊。

時間與日期函數

  • #系統時間函數 now(), current_timestamp, sysdate ()

這三個函數都能傳回系統時間,看看它們的區別,同時存取這三個函數得到的結果一樣,但是讓其等待幾秒之後,結果就不同了。

mysql中生日用什麼類型

上圖發現sysdate()回傳的時間與now(), current_timestamp不同,慢了2秒,原因如下:

current_timestamp 是now的同義詞,兩者實際上是一樣的;

sysdate() 函數傳回的是執行到目前函數時的時間,而now()傳回的是執行sql語句時的時間;

  • 時間加減函數

① date_add 與date_sub

date_add(date, interval expr unit) 與 date_sub( date, interval expr unit) 分別表示增加與減少。

select
date_add('2017-12-18 22:51:00', interval 1 second),
date_add('2017-12-18 22:51:00', interval 1 minute),
date_add('2017-12-18 22:51:00', interval 1 hour),
date_add('2017-12-18 22:51:00', interval 1 day),
date_add('2017-12-18 22:51:00', interval 1 month),
date_add('2017-12-18 22:51:00', interval 1 year);

遇到閏月時,date_add怎麼處理呢,MySQL如果是閏月回傳的日期是2月29,如果不是閏月回傳的是2月28。

select date_add('2016-02-29 22:51:00', interval -1 year),
date_add('2016-02-29 22:51:00', interval 4 year);
2015-02-28 22:51:002020-02-29 22:51:00

② datediff 與 timediff

  • #datediff(date1,date2):兩個日期相減 date1 - date2,傳回天數。 select datediff('2016-02-29 22:51:00','2016-03-29 22:51:00');

  • #timediff(time1,time2):兩個日期相減time1 - time2,傳回time 差值(格式為:838:59:59)。 select timediff('2016-02-29 22:51:00','2016-01-29 22:51:00');

注意:timediff(time1,time2)函數的兩個參數類型必須相同。

③ timestamp 增,減,轉換函數

  • #timestamp(date)  -- date to timestamp

  • #timestamp(date, time) -- date time

  • timestampadd(unit,interval,datetime_expr)   -- 類似date_add,對時間以單位增加,減少

select timestampadd(second, 60, '2017-12-18 00:00:00');
select timestampadd(minute, 60, '2017-12-18 00:00:00');
select timestampadd(hour, 60, '2017-12-18 00:00:00');
  • timestampdiff(unit,datetime_expr1,datetime_expr2) --計算兩個時間的差,可以計算秒,分,時,天,月,年,週

select timestampdiff(second, '2017-12-18 00:00:00','2017-12-18 12:00:00');
select timestampdiff(hour, '2017-11-18 00:00:00','2017-12-18 12:00:00');
select timestampdiff(day, '2017-10-17 00:00:00','2017-11-18 23:00:00');

④ date_format 函數

date_format 函數主要用途是格式化日期

select date_format(now(),'%Y%m%d'),
date_format(now(),'%Y-%m-%d'),
date_format(now(),'%Y/%m/%d'),
date_format(now(),'%m/%d/%Y');

注意:查詢資料時如果對某個日期欄位使用這個函數,則最佳化器不會使用欄位的索引,也不能透過索引來查詢數據,因此查詢效率會很低,如下列查詢語句,如果欄位birth_date有索引,則該索引因為使用了date_format函數失效:

select *
from employees
where date_format(birth_date,'%Y-%m-%d') = '1961-08-03';

#日期的經典程式設計問題

根據某個使用者的出生日期與當前日期,計算他最近的生日(包括已經過去的和將來的)

-- 上一年,当年,下一年生日与当前时间差最小的年就是用户最近的生日
select emp_no,first_name,last_name,birth_date,today,if(last_diff<=cur_diff and last_diff<=next_diff, last,if(cur_diff
from
(
-- 计算当前时间与上一年,当年,下一年生日的时间差
select emp_no,first_name,last_name,birth_date,datediff(today,last) as last_diff,abs(datediff(today,cur)) as cur_diff,abs(datediff(today,next)) as next_diff,
last,cur,next,today
from
(
-- 用户最近的生日可能分布在前一年,当年,下一年。根据年度差year_diff计算上一年,当年,下一年用户的生日
select emp_no,first_name,last_name,birth_date,
DATE_ADD(birth_date,INTERVAL year_diff year) as cur,
DATE_ADD(birth_date,INTERVAL year_diff+1 year) as next,
DATE_ADD(birth_date,INTERVAL year_diff-1 year) as last,
today
from
(
-- 获取当前事前与出生日期的年度差 year_diff
select emp_no,first_name,last_name,birth_date,(year(NOW())-year(birth_date)) as year_diff, NOW() as today
from employees
) a
) b
) c

【相關推薦:mysql視頻教程

以上是mysql中生日用什麼類型的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn