MySQL 8.0 Reference Manual（讀書筆記19節-- 日期與計算）

4月10日，以“Data+AI，構建新質生產力”為主題的袋鼠雲春季發佈會圓滿落幕。大會中，袋鼠雲帶來了一系列“+AI”的數字化產品與最新行業沉澱，旨在將數據與AI緊密結合，打破傳統的生產力邊界，賦能企業實現更高質量、更高效率的數字化發展。 2部白皮書：聚焦行業沉澱《行業指標體系白皮書》：系統闡述了 ...

1.age 與出生日期

為什麼設計的時候，存放的是出生日期而不是年齡呢？這個問題簡單，細想很有意思，也包含著智慧，來自生產生活的思考。下麵的解釋很到位。

How about age? That might be of interest, but it is not a good thing to store in a database. Age changes as time passes, which means you'd have to update your records often. Instead, it is better to store a fixed value such as date of birth. Then, whenever you need age, you can calculate it as the difference between the current date and the birth date. MySQL provides functions for doing date arithmetic, so this is not difficult.

---日期是出生是固化不變的，而年齡是動態變化的。

Storing birth date rather than age has other advantages, too:

• You can use the database for tasks such as generating reminders【riˈmaɪndərz (告知該做某事的)通知單，提示信;引起回憶的事物;提醒人的事物;】 for upcoming birthdays. ( If you think this type of query is somewhat silly【ˈsɪli 愚蠢的;傻的;(尤指像小孩一樣)可笑的，荒唐的，冒傻氣的;沒頭腦的;鬧著玩的;不實用的;不明事理的;】, note that it is the same question you might ask in the context【ˈkɑːntekst 上下文;(事情發生的)背景，環境，來龍去脈;語境;】 of【in the context of 在…的背景下;在…背景下;在……情況下;】 a business database to identify clients to whom you need to send out birthday greetings in the current week or month, for that computer-assisted personal touch. )

• You can calculate age in relation to dates other than the current date. For example, if you store death date in the database, you can easily calculate how old a pet was when it died.

The use of the DATE data type for the birth and death columns is a fairly【ˈferli 相當地;(用以強調)簡直，竟然;公正地;公平合理地;一定地;】 obvious【ˈɑːbviəs 明顯的;顯然的;當然的;公認的;平淡無奇的;易理解的;無創意的;因顯而易見而不必要的;】 choice.

2. 樣本案例

假設設計的一張用來保存寵物【pet】基本信息的表：

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
 species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

數據

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+

3.關於日期的計算需求

MySQL provides several functions that you can use to perform calculations on dates, for example, to calculate ages or extract parts of dates.

3.1 通過TIMESTAMPDIFF()計算時差--求年齡

To determine how many years old each of your pets is, use the TIMESTAMPDIFF() function. Its arguments are the unit【單位】 in which you want the result expressed, and the two dates for which to take the difference. The following query shows, for each pet, the birth date, the current date, and the age in years. An alias (age) is used to make the final output column label more meaningful.

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 |
+----------+------------+------------+------+

計算存活多少時間

A similar query can be used to determine age at death for animals that have died. You determine which animals these are by checking whether the death value is NULL. Then, for those with non-NULL values, compute the difference between the death and birth values:

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 |
+--------+------------+------------+------+

NUll值要特別小心

The query uses death IS NOT NULL rather than death <> NULL because NULL is a special value that cannot be compared using the usual comparison operators.

3.2 計算（刷選出）下個月過生日的記錄

--YEAR(), MONTH(), and DAYOFMONTH()

What if you want to know which animals have birthdays next month? For this type of calculation, year and day are irrelevant; you simply want to extract the month part of the birth column. MySQL provides several functions for extracting parts of dates, such as YEAR(), MONTH(), and DAYOFMONTH(). MONTH() is the appropriate function here.

To see how it works, run a simple query that displays the value of both birth and 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 |
+----------+------------+--------------+

Finding animals with birthdays in the upcoming month is also simple. Suppose that the current month is April. Then the month value is 4 and you can look for animals born in May (month 5) like this: --計算下個月，過生日的；就是簡單的月份+ 1 嗎？

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

There is a small complication if the current month is December. You cannot merely add one to the month number (12) and look for animals born in month 13, because there is no such month. Instead, you look for animals born in January (month 1). --顯然是不合理的，如果12月，怎麼嗎? 簡單+1，不是13月了嗎？這是個笑話。

You can write the query so that it works no matter what the current month is, so that you do not have to use the number for a particular month. DATE_ADD() enables you to add a time interval to a given date. If you add a month to the value of CURDATE(), then extract the month part with MONTH(), the result produces the month in which to look for birthdays:

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

另外一個解法，就是藉助mod()

mod(),取模運算函數，返回兩個數相除的餘數。

A different way to accomplish the same task is to add 1 to get the next month after the current one after using the modulo function (MOD) to wrap【[ræp 包;裹(禮物等);（使文字）換行;用…包裹(或包扎、覆蓋等);用…纏繞(或圍緊);】 the month value to 0 if it is currently 12:

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

MONTH() returns a number between 1 and 12. And MOD(something,12) returns a number between 0 and 11. So the addition has to be after the MOD(), otherwise we would go from November (11) to January (1).

---官網第三章《Tutorial 》