SQL Tutorial SQL Advanced SQL Database SQL References

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:

EmpIDNameLoginStampLogoutStamp
1John2019-10-25 09:20:382019-10-25 17:42:55
2Marry2019-10-25 09:21:052019-10-25 17:43:23
3Jo2019-10-25 09:24:352019-10-25 17:48:56
4Kim2019-10-25 09:25:242019-10-25 17:49:12
5Ramesh2019-10-25 09:27:162019-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:

EmpIDNameLoginStampLogoutStampTIMESTAMPDIFF_Value
1John2019-10-25 09:20:382019-10-25 17:42:558
2Marry2019-10-25 09:21:052019-10-25 17:43:238
3Jo2019-10-25 09:24:352019-10-25 17:48:568
4Kim2019-10-25 09:25:242019-10-25 17:49:128
5Ramesh2019-10-25 09:27:162019-10-25 17:42:088

❮ MySQL Functions