MySQL提供了幾個函數,可以用來計算日期,例如,計算年齡或提取日期部分。
要想確定每個寵物有多大,可以計算當前日期的年和出生日期之間的差。如果當前日期的日歷年比出生日期早,則減去一年。以下查詢顯示了每個寵物的出生日期、當前日期和年齡數值的年數字。
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> 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 |
+----------+------------+------------+------+
此處,YEAR()提取日期的年部分,RIGHT()提取日期的MM-DD (日歷年)部分的最右面5個字符。比較MM-DD值的表達式部分的值一般為1或0,如果CURDATE()的年比birth的年早,則年份應減去1。整個表達式有些難懂,使用alias (age)來使輸出的列標記更有意義。
盡管查詢可行,如果以某個順序排列行,則能更容易地瀏覽結果。添加ORDER BY name子句按照名字對輸出進行排序則能夠實現。
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> 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(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> 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,
-> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
-> 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是特殊的值,不能使用普通比較符來比較,以后會給出解釋。參見3.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月,那么月值是4,你可以找在5月出生的動物 (5月),方法是:
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
如果當前月份是12月,就有點復雜了。你不能只把1加到月份數(12)上并尋找在13月出生的動物,因為沒有這樣的月份。相反,你應尋找在1月出生的動物(1月) 。
你甚至可以編寫查詢,不管當前月份是什么它都能工作。采用這種方法不必在查詢中使用一個特定的月份,DATE_ADD( )允許在一個給定的日期上加上時間間隔。如果在NOW( )值上加上一個月,然后用MONTH()提取月份,結果產生生日所在月份:
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
完成該任務的另一個方法是加1以得出當前月份的下一個月(在使用取模函數(MOD)后,如果月份當前值是12,則“回滾”到值0):
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
注意,MONTH返回在1和12之間的一個數字,且MOD(something,12)返回在0和11之間的一個數字,因此必須在MOD( )以后加1,否則我們將從11月( 11 )跳到1月(1)。