mysql中生日日期怎么表示

這篇文章主要介紹了mysql中生日日期怎么表示的相關知識，內容詳細易懂，操作簡單快捷，具有一定借鑒價值，相信大家閱讀完這篇mysql中生日日期怎么表示文章都會有所收獲，下面我們一起來看看吧。

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中適合生日的日期與時間類型

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：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()

這三個函數都能返回系統時間，看看它們的區別，同時訪問這三個函數得到的結果一樣，但是讓其等待幾秒之后，結果就不同了。

上圖發現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中生日日期怎么表示”知識都有一定的了解，大家如果還想學習更多知識，歡迎關注億速云行業資訊頻道。