MySQL date型別

2019-10-16 22:58:35

在本教學中,我們將向您介紹MySQL DATE資料型別,並演示一些有用的日期函式來有效處理日期資料。

MySQL DATE資料型別簡介

MySQL DATE是用於管理日期值的五種時間資料型別之一。 MySQL使用yyyy-mm-dd格式儲存日期值。此格式是固定的,不可能更改它。

例如,您可能更喜歡使用mm-dd-yyyy格式,但是遺憾,不能直接使用。 一個代替的辦法:遵循標準日期格式,並使用DATE_FORMAT函式按所需格式來格式化日期。

MySQL使用3個位元組來儲存DATE值。DATE值的範圍為1000-01-019999-12-31。 如果要儲存超出此範圍的日期值,則需要使用非時間資料型別,例如整數,例如使用三列,分別儲存年,月和日的資料。還需要建立儲存函式來模擬MySQL提供的內建日期函式,這是不推薦的。

當嚴格模式被禁用時,MySQL將任何無效日期(例如2015-02-30)轉換為零日期值0000-00-00

MySQL日期值為兩位數年份

MySQL使用四位數位儲存日期值的年份。 如果您使用兩位數的年份值,MySQL仍會接受以下規則:

  • 年份值在00-69範圍內轉換為2000-2069
  • 70-99的年值被轉換為1970 - 1999年。

但是,具有兩位數位的日期值是不明確的,因此您應避免使用它。

現在,讓我們來看下面的例子。

首先,建立一個名為people表,其生日(birth_date)列使用DATE資料型別。

USE testdb;

CREATE TABLE people (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE NOT NULL
);

接下來,將一行插入people表中。

INSERT INTO people(first_name,last_name,birth_date)
VALUES('Max','Su','1992-10-11');

然後,查詢people表中的資料,如下所示 -

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    people;

執行上面查詢語句,得到以下結果 -

+------------+-----------+------------+
| first_name | last_name | birth_date |
+------------+-----------+------------+
| Max        | Su        | 1992-10-11 |
+------------+-----------+------------+
1 row in set

之後,使用兩位數的年份格式將資料插入到people表中。

INSERT INTO people(first_name,last_name,birth_date)
VALUES('Jack','Daniel','01-09-01'),
      ('Lily','Bush','80-09-01');

在第一行,我們使用01(範圍在00-69)作為年份,所以MySQL將其轉換為2001年。在第二行,我們使用80(範圍70-99)作為年份,MySQL將其轉換為1980年。

最後,從people表查詢資料,以檢查資料是否根據轉換規則進行轉換。

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    people;

執行上面查詢語句,得到以下結果 -

+------------+-----------+------------+
| first_name | last_name | birth_date |
+------------+-----------+------------+
| Max        | Su        | 1992-10-11 |
| Jack       | Daniel    | 2001-09-01 |
| Lily       | Bush      | 1980-09-01 |
+------------+-----------+------------+
3 rows in set

MySQL DATE函式

MySQL提供了許多有用的日期功能,可以有效地操作日期。

要獲取當前日期和時間,請使用NOW()函式。

SELECT NOW() as cur_datetime;

執行上面查詢語句,得到以下結果 -

mysql> SELECT NOW() as cur_datetime;
+---------------------+
| cur_datetime        |
+---------------------+
| 2017-07-25 21:51:54 |
+---------------------+
1 row in set

要獲取DATETIME值的日期部分,可以使用DATE()函式。

SELECT DATE(NOW());

執行上面查詢語句,得到以下結果 -

mysql> SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2017-07-25  |
+-------------+
1 row in set

要獲取當前的系統日期,可以使用CURDATE()函式,如下所示:

SELECT CURDATE();

執行上面查詢語句,得到以下結果 -

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2017-07-25 |
+------------+
1 row in set

要格式化日期值,可以使用DATE_FORMAT函式。以下語句使用日期格式模式%m/%d/%Y,格式化日期為:mm/dd/yyyy

SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') today;

執行上面查詢語句,得到以下結果 -

mysql> SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') today;
+------------+
| today      |
+------------+
| 07/25/2017 |
+------------+
1 row in set

要計算兩個日期值之間的天數,可以使用DATEDIFF函式,如下所示:

SELECT DATEDIFF('2015-11-04','2014-11-04') days;

執行上面查詢語句,得到以下結果 -



要新增幾天,幾週,幾個月,幾年等到一個日期值,可以使用DATE_ADD函式:

SELECT 
    '2018-01-01' start,
    DATE_ADD('2018-01-01', INTERVAL 1 DAY) 'one day later',
    DATE_ADD('2018-01-01', INTERVAL 1 WEEK) 'one week later',
    DATE_ADD('2018-01-01', INTERVAL 1 MONTH) 'one month later',
    DATE_ADD('2018-01-01', INTERVAL 1 YEAR) 'one year later';

執行上面查詢語句,得到以下結果 -

+------------+---------------+----------------+-----------------+----------------+
| start      | one day later | one week later | one month later | one year later |
+------------+---------------+----------------+-----------------+----------------+
| 2018-01-01 | 2018-01-02    | 2018-01-08     | 2018-02-01      | 2019-01-01     |
+------------+---------------+----------------+-----------------+----------------+
1 row in set

類似地,可以使用DATE_SUB函式從日期中減去間隔值:

SELECT 
    '2018-01-01' start,
    DATE_SUB('2018-01-01', INTERVAL 1 DAY) 'one day before',
    DATE_SUB('2018-01-01', INTERVAL 1 WEEK) 'one week before',
    DATE_SUB('2018-01-01', INTERVAL 1 MONTH) 'one month before',
    DATE_SUB('2018-01-01', INTERVAL 1 YEAR) 'one year before';

執行上面查詢語句,得到以下結果 -

+------------+----------------+-----------------+------------------+-----------------+
| start      | one day before | one week before | one month before | one year before |
+------------+----------------+-----------------+------------------+-----------------+
| 2018-01-01 | 2017-12-31     | 2017-12-25      | 2017-12-01       | 2017-01-01      |
+------------+----------------+-----------------+------------------+-----------------+
1 row in set

如果要獲取日期值的日期,月份,季度和年份,可以使用相應的函式:DAYMONTHQUARTERYEAR,如下所示:

SELECT DAY('2018-12-31') day, 
       MONTH('2018-12-31') month, 
       QUARTER('2018-12-31') quarter, 
       YEAR('2018-12-31') year;

執行上面查詢語句,得到以下結果 -


mysql> SELECT DAY('2018-12-31') day, 
       MONTH('2018-12-31') month, 
       QUARTER('2018-12-31') quarter, 
       YEAR('2018-12-31') year;
+-----+-------+---------+------+
| day | month | quarter | year |
+-----+-------+---------+------+
|  31 |    12 |       4 | 2018 |
+-----+-------+---------+------+
1 row in set

獲得周資訊週相關功能。例如,WEEK函式返回週數,WEEKDAY函式返回工作日索引,WEEKOFYEAR函式返回周日曆。

SELECT 
    WEEKDAY('2018-12-31') weekday,
    WEEK('2018-12-31') week,
    WEEKOFYEAR('2018-12-31') weekofyear;

執行上面查詢語句,得到以下結果 -

mysql> SELECT 
    WEEKDAY('2018-12-31') weekday,
    WEEK('2018-12-31') week,
    WEEKOFYEAR('2018-12-31') weekofyear;
+---------+------+------------+
| weekday | week | weekofyear |
+---------+------+------------+
|       0 |   52 |          1 |
+---------+------+------------+
1 row in set

如果沒有傳遞第二個引數,或者如果傳遞引數為0,則week函式將返回帶有零的索引的週數。如果傳遞引數為1,則將返回1索引的週數。

SELECT 
    WEEKDAY('2018-12-31') weekday,
    WEEK('2018-12-31',1) week,
    WEEKOFYEAR('2018-12-31') weekofyear;

執行上面查詢語句,得到以下結果 -

mysql> SELECT 
    WEEKDAY('2018-12-31') weekday,
    WEEK('2018-12-31',1) week,
    WEEKOFYEAR('2018-12-31') weekofyear;
+---------+------+------------+
| weekday | week | weekofyear |
+---------+------+------------+
|       0 |   53 |          1 |
+---------+------+------------+
1 row in set

在本教學中,您已經了解並學習了MySQL DATE資料型別以及如何使用一些有用的日期函式來操作日期值。