MySQL :: MySQL 9.0 參考手冊 :: 5.3.4.5 日期計算

版本 9.0

8.4 目前版本
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 日期計算

5.3.4.5 日期計算

MySQL 提供數個函數，您可以使用這些函數對日期執行計算，例如計算年齡或擷取日期的一部分。

若要判斷您的每隻寵物幾歲，請使用 TIMESTAMPDIFF() 函數。其引數是您希望結果表示的單位，以及要計算差異的兩個日期。以下查詢顯示每隻寵物的出生日期、目前日期和以年為單位的年齡。使用別名 (age) 讓最終的輸出欄標籤更有意義。

mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
+----------+------------+------------+------+

查詢可以運作，但如果將列以某種順序呈現，結果可以更容易掃描。這可以透過新增 ORDER BY name 子句，依名稱排序輸出，來完成。

mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet ORDER BY name;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
+----------+------------+------------+------+

若要依 age 而非 name 排序輸出，只需使用不同的 ORDER BY 子句。

mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet ORDER BY age;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
+----------+------------+------------+------+

類似的查詢可用於判斷已死亡動物的死亡年齡。您可以使用檢查 death 值是否為 NULL 來判斷哪些動物屬於此類。然後，對於具有非 NULL 值的動物，計算 death 和 birth 值之間的差異。

mysql> SELECT name, birth, death,
       TIMESTAMPDIFF(YEAR,birth,death) AS age
       FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 |    5 |
+--------+------------+------------+------+

查詢使用 death IS NOT NULL 而不是 death <> NULL，因為 NULL 是一個特殊值，無法使用一般的比較運算子進行比較。稍後將討論此問題。請參閱第 5.3.4.6 節，「使用 NULL 值」。

如果您想知道哪些動物的生日在下個月，該怎麼辦？對於這種類型的計算，年份和日期並不重要；您只需要擷取 birth 欄的月份部分。MySQL 提供數個函數用於擷取日期的部分，例如 YEAR()、MONTH() 和 DAYOFMONTH()。MONTH() 是這裡適用的函數。若要了解其運作方式，請執行一個簡單的查詢，顯示 birth 和 MONTH(birth) 的值

mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+

尋找下個月生日的動物也很簡單。假設目前月份是四月。那麼月份值為 4，您可以像這樣尋找五月出生的動物（月份 5）

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

如果目前月份是十二月，則會稍微複雜。您不能只是在月份數字 (12) 上加一，然後尋找在月份 13 出生的動物，因為沒有這個月份。而是要尋找在一月出生的動物（月份 1）。

您可以編寫查詢，使其無論目前的月份為何都能運作，這樣您就不必使用特定月份的數字。DATE_ADD() 可讓您在給定的日期新增時間間隔。如果您在 CURDATE() 的值上加一個月，然後使用 MONTH() 擷取月份部分，結果會產生要尋找生日的月份。

mysql> SELECT name, birth FROM pet
       WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

完成相同任務的另一種方法是，在使用模數函數 (MOD) 將月份值包裝為 0 (如果目前為 12) 之後，加上 1 來取得目前月份後的下一個月份。

mysql> SELECT name, birth FROM pet
       WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

MONTH() 會傳回介於 1 和 12 之間的數字。MOD(something,12) 會傳回介於 0 和 11 之間的數字。因此，加法必須在 MOD() 之後，否則我們將從十一月 (11) 到一月 (1)。

如果計算使用無效的日期，則計算會失敗並產生警告

mysql> SELECT '2018-10-31' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-31' + INTERVAL 1 DAY |
+-------------------------------+
| 2018-11-01                    |
+-------------------------------+
mysql> SELECT '2018-10-32' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-32' + INTERVAL 1 DAY |
+-------------------------------+
| NULL                          |
+-------------------------------+
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2018-10-32' |
+---------+------+----------------------------------------+