TIMESTAMPDIFF
Syntax
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
Description
Returns datetime_expr2
- datetime_expr1
, where datetime_expr1
and
datetime_expr2
are date or datetime expressions. One expression may be
a date and the other a datetime; a date value is treated as a datetime
having the time part '00:00:00' where necessary. The unit for the
result (an integer) is given by the unit argument. The legal values
for unit are the same as those listed in the description of the
TIMESTAMPADD() function, i.e MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
TIMESTAMPDIFF
can also be used to calculate age.
Examples
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); +------------------------------------------------+ | TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') | +------------------------------------------------+ | 3 | +------------------------------------------------+ SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); +-----------------------------------------------+ | TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01') | +-----------------------------------------------+ | -1 | +-----------------------------------------------+ SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); +----------------------------------------------------------+ | TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55') | +----------------------------------------------------------+ | 128885 | +----------------------------------------------------------+
Calculating age:
SELECT CURDATE(); +------------+ | CURDATE() | +------------+ | 2019-05-27 | +------------+ SELECT TIMESTAMPDIFF(YEAR, '1971-06-06', CURDATE()) AS age; +------+ | age | +------+ | 47 | +------+ SELECT TIMESTAMPDIFF(YEAR, '1971-05-06', CURDATE()) AS age; +------+ | age | +------+ | 48 | +------+
Age as of 2014-08-02:
SELECT name, date_of_birth, TIMESTAMPDIFF(YEAR,date_of_birth,'2014-08-02') AS age FROM student_details; +---------+---------------+------+ | name | date_of_birth | age | +---------+---------------+------+ | Chun | 1993-12-31 | 20 | | Esben | 1946-01-01 | 68 | | Kaolin | 1996-07-16 | 18 | | Tatiana | 1988-04-13 | 26 | +---------+---------------+------+
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.