MySQL :: MySQL 5.1 リファレンスマニュアル (オンラインヘルプ)

MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 1 チュートリアル :: 1.3 データベースの作成と使用 :: 1.3.4 テーブルからの情報の取り出し :: 1.3.4.5 日付の計算

Section Navigation [Toggle]

1.3.4.5. 日付の計算

MySQL には、年齢の計算や日付の一部の抽出など、日付の計算に使用できる関数がいくつか用意されています。

各ペットの年齢を求めるには、現在の日付と誕生日について年の部分の差を計算してから、暦年で現在の日付が誕生日の日付より早ければ 1 を減算します。次のクエリーでは、各ペットの誕生日、現在の日付、および年齢が表示されます。

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() は日付の右端の 5 文字 (暦年の MM-DD に相当) を抽出します。式のうち、MM-DD の値を比較する部分の結果は、1 または 0 になります。これにより、その年の中で CURDATE() が birth より早ければ、1 歳少なく調整されます。式全体を表示するとやや不恰好なので、エイリアス (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 |
+----------+------------+------------+------+

出力を name ではなく age でソートするには、異なる 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 |
+--------+------------+------------+------+

NULL は通常の比較演算子を使用して比較することのできない特殊な値なので、このクエリーでは death <> NULL ではなく death IS NOT NULL を使用しています。これについてはあとで説明します。項1.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 月の場合は多少複雑になります。月の番号 (12) に単に 1 を加算して 13 月に生まれたペットを探すということはできません。そのような月は存在しないからです。代わりに、1 月 (月 1) に生まれたペットを探します。

現在が何月であっても機能するクエリーを記述すると、特定の月の番号を使用する必要がなくなります。DATE_ADD() を使用すると、特定の日付に時間間隔を加算できます。CURDATE() の値に 1 か月を加算してから、月の部分を MONTH() で抽出すると、誕生日を調べる月が得られます。

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

現在の月の値にモジュロ関数 (MOD) を適用して現在 12 の場合は 0 に折り返してから、1 を加算する方法でも、同じ結果が得られます。

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

MONTH() は 1 から 12 までの数値を返します。また、MOD(something,12) は 0 から 11 までの数値を返します。したがって、MOD() のあとで加算を行わないと、11 月から 1 月に進んでしまいます。

Top / Previous / Next / Up / Table of Contents