MySQL date類型

在本教學中，我們將向您介紹MySQL DATE數據類型，並演示一些有用的日期函數來有效處理日期數據。

MySQL DATE數據類型簡介

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

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

MySQL使用3個位元組來存儲DATE值。DATE值的範圍為1000-01-01到9999-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

如果要獲取日期值的日期，月份，季度和年份，可以使用相應的函數：DAY，MONTH，QUARTER和YEAR，如下所示：

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數據類型以及如何使用一些有用的日期函數來操作日期值。