MySQL TIMESTAMPDIFF() Function

The MySQL TIMESTAMPDIFF() function returns the difference of two date or datetime value. While calculating the difference one argument can be date value and other argument can be a datetime value. The date value will be treated having the time part '00:00:00' if necessary. The unit for the result (an integer) is given by the unit argument.

Syntax

TIMESTAMPDIFF(unit, datetime1, datetime2)

Parameters

`datetime1`	`Required.` Specify a date or datetime value.
`datetime2`	`Required.` Specify a date or datetime value.
`unit`	`Required.` Specify the unit type of the difference. It can be one of the following values: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR

Return Value

Returns (datetime2 − datetime1). The unit for the result is given by the unit argument.

Example 1:

The example below shows the usage of TIMESTAMPDIFF() function.

mysql> SELECT TIMESTAMPDIFF(DAY, '2019-10-15', '2019-10-25');
Result: 10

mysql> SELECT TIMESTAMPDIFF(DAY, '2019-10-15 10:20:25', '2019-10-25 12:40:45');
Result: 10

mysql> SELECT TIMESTAMPDIFF(HOUR, '2019-10-25 10:20:25', '2019-10-25 12:40:45');
Result: 2

mysql> SELECT TIMESTAMPDIFF(MINUTE, '2019-10-25 10:20:25', '2019-10-25 12:40:45');
Result: 140

mysql> SELECT TIMESTAMPDIFF(SECOND, '2019-10-25 10:20:25', '2019-10-25 12:40:45');
Result: 8420

mysql> SELECT TIMESTAMPDIFF(MICROSECOND, '2019-10-25 10:20:25', '2019-10-25 12:40:45');
Result: 8420000000

mysql> SELECT TIMESTAMPDIFF(WEEK, '2018-10-25', '2020-03-25');
Result: 73

mysql> SELECT TIMESTAMPDIFF(MONTH, '2018-10-25', '2020-03-25');
Result: 17

mysql> SELECT TIMESTAMPDIFF(QUARTER, '2018-10-25', '2020-03-25');
Result: 5

mysql> SELECT TIMESTAMPDIFF(YEAR, '2018-10-25', '2020-03-25');
Result: 1

Example 2:

Consider a database table called EmployeeLogin with the following records:

EmpID	Name	LoginStamp	LogoutStamp
1	John	2019-10-25 09:20:38	2019-10-25 17:42:55
2	Marry	2019-10-25 09:21:05	2019-10-25 17:43:23
3	Jo	2019-10-25 09:24:35	2019-10-25 17:48:56
4	Kim	2019-10-25 09:25:24	2019-10-25 17:49:12
5	Ramesh	2019-10-25 09:27:16	2019-10-25 17:42:08

To calculate the difference between records of LoginStamp column and LogoutStamp column, the following query can be used:

SELECT *, 
TIMESTAMPDIFF(HOUR, LoginStamp, LogoutStamp) AS TIMESTAMPDIFF_Value
FROM EmployeeLogin;

This will produce a result similar to:

EmpID	Name	LoginStamp	LogoutStamp	TIMESTAMPDIFF_Value
1	John	2019-10-25 09:20:38	2019-10-25 17:42:55	8
2	Marry	2019-10-25 09:21:05	2019-10-25 17:43:23	8
3	Jo	2019-10-25 09:24:35	2019-10-25 17:48:56	8
4	Kim	2019-10-25 09:25:24	2019-10-25 17:49:12	8
5	Ramesh	2019-10-25 09:27:16	2019-10-25 17:42:08	8

❮ MySQL Functions