TIMESTAMPDIFF()

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.

This function returns NULL if datetime_expr1 or datetime_expr2 is NULL.

Syntax

  1. > TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Arguments

ArgumentsDescription
unitis a string representing the unit of the time interval. This can be MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH or YEAR etc.
datetime_expr1,datetime_expr2Mandatory parameters. datetime_expr1 and datetime_expr2 expressions need to be of the same type. datetime_expr1 and datetime_expr2 are strings converted to TIME or DATETIME expressions. Returns NULL if datetime_expr1 or datetime_expr2 is NULL.

Examples

  • Example 1:
  1. mysql> SELECT TIMESTAMPDIFF( MICROSECOND, '2017-12-01 12:15:12','2018-01-01 7:18:20');
  2. +---------------------------------------------------------------------+
  3. | timestampdiff(microsecond, 2017-12-01 12:15:12, 2018-01-01 7:18:20) |
  4. +---------------------------------------------------------------------+
  5. |                                                       2660588000000 |
  6. +---------------------------------------------------------------------+
  7. 1 row in set (0.00 sec)
  • Example 2:
  1. drop table if exists t1;
  2. create table t1(a date,  b date);
  3. insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12');
  4. insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12');
  5. insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12');
  6. insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12');
  7. insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12');
  8. insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12');
  9. insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12');
  10. insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12');
  11. insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12');
  12. insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12');
  14. mysql> SELECT a, b, TIMESTAMPDIFF(MICROSECOND, a, b) from t1;
  15. +------------+------------+----------------------------------+
  16. | a          | b          | timestampdiff(microsecond, a, b) |
  17. +------------+------------+----------------------------------+
  18. | 2019-11-01 | 2018-01-01 |                  -57801600000000 |
  19. | 2019-10-01 | 2018-01-01 |                  -55123200000000 |
  20. | 2020-10-01 | 2018-01-01 |                  -86745600000000 |
  21. | 2021-11-01 | 2018-01-01 |                 -120960000000000 |
  22. | 2022-01-01 | 2018-01-01 |                 -126230400000000 |
  23. | 2018-01-01 | 2019-11-01 |                   57801600000000 |
  24. | 2018-01-01 | 2019-10-01 |                   55123200000000 |
  25. | 2018-01-01 | 2020-10-01 |                   86745600000000 |
  26. | 2018-01-01 | 2021-11-01 |                  120960000000000 |
  27. | 2018-01-01 | 2022-01-01 |                  126230400000000 |
  28. +------------+------------+----------------------------------+
  29. 10 rows in set (0.00 sec)