MySQL 8.0 Reference Manual（读书笔记19节-- 日期与计算）

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 》