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 》