MySQL Timestampdiff

The MySQL TIMESTAMPDIFF() function is one of the MySQL Date Functions which returns the difference between two dates or DateTime expressions..

Syntax of TIMESTAMPDIFF()

TIMESTAMPDIFF(unit,datetime1,datetime2)

Where, the unit is the timestamp return unit. This function subtracts second argument from the third, i.e., (datetime2 - datetime1). It is not mandatory that both the expression are of the same type. Like - we can get the difference between DATE and DATETIME value. In case you use a DATE value, the TIMESTAMPDIFF function treats it as a DATETIME value whose time part is '00:00:00'.

MySQL Timestampdiff Exercise

Write a mysql statement to determine the age of each of the students.
Suppose the table is -

+----+--------------+------------+------------+
| id | name         | department | birth      |
+----+--------------+------------+------------+
|  1 | Maria Gloria | CS         | 1994-03-12 |
|  2 | John Smith   | IT         | 1993-02-07 |
|  3 | Gal Rao      | CS         | 1992-09-11 |
|  4 | Jakey Smith  | EC         | 1990-08-31 |
|  5 | Rama Saho    | IT         | 1994-12-09 |
|  6 | Maria Gaga   | EC         | 1993-10-09 |
+----+--------------+------------+------------+

Solution

To determine how many years old each of the student is, use the TIMESTAMPDIFF() function. It returns a value after subtracting a datetime expression from another. Both datetime or date expressions are required parameters.

The following query shows student data and age of each students in years.

 mysql> SELECT *,
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM students;

Output of the above statement -

+----+--------------+------------+------------+------+
| id | name         | department | birth      | age  |
+----+--------------+------------+------------+------+
|  1 | Maria Gloria | CS         | 1994-03-12 |   24 |
|  2 | John Smith   | IT         | 1993-02-07 |   25 |
|  3 | Gal Rao      | CS         | 1992-09-11 |   25 |
|  4 | Jakey Smith  | EC         | 1990-08-31 |   27 |
|  5 | Rama Saho    | IT         | 1994-12-09 |   23 |
|  6 | Maria Gaga   | EC         | 1993-10-09 |   24 |
+----+--------------+------------+------------+------+