Database Reference
In-Depth Information
TIMESTAMPDIFF()
handles the calculations necessary to adjust for differing month and
year lengths and relative positions of the dates within the calendar year. Suppose that a
sibling
table lists the birth dates of Gretchen Smith and her brothers Wilbur and Franz:
mysql>
SELECT * FROM sibling;
+----------+------------+
| name | birth |
+----------+------------+
| Gretchen | 1942-04-14 |
| Wilbur | 1946-11-28 |
| Franz | 1953-03-05 |
+----------+------------+
Using
TIMESTAMPDIFF()
, you can answer questions such as these:
• How old are the Smith children today, in years and months?
mysql>
SELECT name, birth, CURDATE() AS today,
->
TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS 'age in years',
->
TIMESTAMPDIFF(MONTH,birth,CURDATE()) AS 'age in months'
->
FROM sibling;
+----------+------------+------------+--------------+---------------+
| name | birth | today | age in years | age in months |
+----------+------------+------------+--------------+---------------+
| Gretchen | 1942-04-14 | 2014-02-20 | 71 | 862 |
| Wilbur | 1946-11-28 | 2014-02-20 | 67 | 806 |
| Franz | 1953-03-05 | 2014-02-20 | 60 | 731 |
+----------+------------+------------+--------------+---------------+
• How old were Gretchen and Wilbur when Franz was born, in years and months?
mysql>
SELECT name, birth, '1953-03-05' AS 'Franz'' birth',
->
TIMESTAMPDIFF(YEAR,birth,'1953-03-05') AS 'age in years',
->
TIMESTAMPDIFF(MONTH,birth,'1953-03-05') AS 'age in months'
->
FROM sibling WHERE name <> 'Franz';
+----------+------------+--------------+--------------+---------------+
| name | birth | Franz' birth | age in years | age in months |
+----------+------------+--------------+--------------+---------------+
| Gretchen | 1942-04-14 | 1953-03-05 | 10 | 130 |
| Wilbur | 1946-11-28 | 1953-03-05 | 6 | 75 |
+----------+------------+--------------+--------------+---------------+
6.14. Finding the First Day, Last Day, or Length of a Month
Problem
Given a date, you want to determine the date for the first or last day of the month in
which the date occurs, or the first or last day for the month
n
months away. A related
problem is to determine the number of days in a month.
