top
Loading...
3.3.4.5.日期計算
3.3.4.5. 日期計算

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值的表達式部分的值一般為10,如果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值的動物,需要計算出deathbirth值之間的差:

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()是適合的函數。為了看它怎樣工作,運行一個簡單的查詢,顯示birthMONTH(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返回在112之間的一個數字,且MOD(something,12)返回在011之間的一個數字,因此必須在MOD( )以后加1,否則我們將從11( 11 )跳到1(1)

作者:mysql.com
來源:http://dev.mysql.com/doc/refman/5.1/zh/tutorial.html
北斗有巢氏 有巢氏北斗