(mysql.info.gz) Date calculations

 
 3.3.4.5 Date Calculations
 .........................
 
 MySQL provides several functions that you can use to perform
 calculations on dates, for example, to calculate ages or extract parts
 of dates.
 
 To determine how many years old each of your pets is, compute the
 difference in the year part of the current date and the birth date, then
 subtract one if the current date occurs earlier in the calendar year
 than the birth date.  The following query shows, for each pet, the
 birth date, the current date, and the age in years.
 
      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 |
      +----------+------------+------------+------+
 
 Here, `YEAR()' pulls out the year part of a date and `RIGHT()' pulls
 off the rightmost five characters that represent the `MM-DD' (calendar
 year) part of the date.  The part of the expression that compares the
 `MM-DD' values evaluates to 1 or 0, which adjusts the year difference
 down a year if `CURDATE()' occurs earlier in the year than `birth'.
 The full expression is somewhat ungainly, so an alias (`age') is used
 to make the output column label more meaningful.
 
 The query works, but the result could be scanned more easily if the rows
 were presented in some order.  This can be done by adding an `ORDER BY
 name' clause to sort the output 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 |
      +----------+------------+------------+------+
 
 To sort the output by `age' rather than `name', just use a different
 `ORDER BY' clause:
 
      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 |
      +----------+------------+------------+------+
 
 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,
          -> (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 |
      +--------+------------+------------+------+
 
 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.  This is discussed later.   Working with
 `NULL' Working with NULL.
 
 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
 date-part extraction functions, 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 easy, too.
 Suppose that the current month is April.  Then the month value is `4'
 and you look for animals born in May (month `5') like this:
 
      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
 don't just 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').
 
 You can even write the query so that it works no matter what the current
 month is.  That way you don't have to use a particular month number in
 the query.  `DATE_ADD()' allows 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));
 
 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 around the month value to `0' if it is currently `12'):
 
      mysql> SELECT name, birth FROM pet
          -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
 
 Note that `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').
Info Catalog
(mysql.info.gz) Sorting rows
(mysql.info.gz) Retrieving data
(mysql.info.gz) Working with NULL
automatically generated byinfo2html