Date Calculations

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, use the TIMESTAMPDIFF() [1244] function.
Its arguments are the unit in which you want the result expressed, and the two date 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 |
+----------+------------+------------+------+
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(),
- TIMESTAMPDIFF(YEAR,birth,CURDATE()) 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(),
- TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
- FROM pet ORDER BY age;
... zobacz całą notatkę