TIMESTAMPDIFF()

Function Description

TIMESTAMPEDIFF() returns an integer representing the time interval between the first date-time expression and the second date-time expression within a given time unit. That is, the difference between datetime_expr2 and datetime_expr1. datetime_expr1 and datetime_expr2 are date or datetime expressions; one expression can be a date, the other expression can be a datetime, and the date value is considered a datetime with the time part 00:00:00.

If datetime_expr1 or datetime_expr2 is NULL, this function returns NULL.

Function Syntax

> TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Parameter definition

Parameters	Description
unit	is a string representing the unit of time interval. This can be MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH or YEAR, etc.
datetime_expr1,datetime_expr2	Required parameters. The datetime_expr1 and datetime_expr2 expressions need to have 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.

Example

• Example 1:

mysql> SELECT TIMESTAMPDIFF( MICROSECOND, '2017-12-01 12:15:12','2018-01-01 7:18:20');
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| timestampdiff(microsecond, 2017-12-01 12:15:12, 2018-01-01 7:18:20) |
+------------------------------------------------------------------------------------------------------------------------------
| 2660588000000 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

• Example 2:

drop table if exists t1;
create table t1(a date, b date);
insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12');
insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12');
insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12');
insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12');
insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12');
insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12');
insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12');
insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12');
insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12');
insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12');

mysql> SELECT a, b, TIMESTAMPDIFF(MICROSECOND, a, b) from t1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| a | b | timestampdiff(microsecond, a, b) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2019-11-01 | 2018-01-01 | -57801600000000 |
| 2019-10-01 | 2018-01-01 | -55123200000000 |
| 2020-10-01 | 2018-01-01 | -86745600000000 |
| 2021-11-01 | 2018-01-01 | -1209600000000000 |
| 2022-01-01 | 2018-01-01 | -126230400000000 |
| 2018-01-01 | 2019-11-01 | 57801600000000 |
| 2018-01-01 | 2019-10-01 | 55123200000000 |
| 2018-01-01 | 2020-10-01 | 86745600000000 |
| 2018-01-01 | 2021-11-01 | 1209600000000000 |
| 2018-01-01 | 2022-01-01 | 126230400000000 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10 rows in set (0.00 sec)