MySQL | TIMESTAMPDIFF method
Start your free 7-days trial now!
MySQL's TIMESTAMPDIFF(~) method returns the time difference between two date or datetime expressions in the specified unit. (i.e. datetime_expr2 - datetime_expr1).
Parameters
1. unit | unit
The unit to return the time difference in.
Possible unit values |
|---|
MICROSECOND |
SECOND |
MINUTE |
HOUR |
DAY |
WEEK |
MONTH |
QUARTER |
YEAR |
2. datetime_expr1 | date/datetime
The date/datetime value to subtract.
3. datetime_expr2 | date/datetime
The date/datetime to be subtracted from.
Return value
The time difference between the two input date/datetime expressions in the specified time unit.
Examples
Consider the following table about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
Basic usage
To return the difference between student enrollment dates and the start of the millennium in months:
SELECT fname, day_enrolled, TIMESTAMPDIFF(MONTH, '2000-01-01', day_enrolled)FROM students;
+----------+--------------+--------------------------------------------------+| fname | day_enrolled | TIMESTAMPDIFF(MONTH, '2000-01-01', day_enrolled) |+----------+--------------+--------------------------------------------------+| Sky | 2015-12-03 | 191 || Ben | 2016-04-20 | 195 || Travis | 2018-08-14 | 223 || Arthur | 2016-04-01 | 195 || Benjamin | 2014-01-01 | 168 |+----------+--------------+--------------------------------------------------+
To return the difference between '2016-04-29 18:00:00' and student enrollment dates in hours:
SELECT fname, day_enrolled, TIMESTAMPDIFF(HOUR, day_enrolled, '2016-04-29 18:00:00')FROM students;
+----------+--------------+----------------------------------------------------------+| fname | day_enrolled | TIMESTAMPDIFF(HOUR, day_enrolled, '2016-04-29 18:00:00') |+----------+--------------+----------------------------------------------------------+| Sky | 2015-12-03 | 3570 || Ben | 2016-04-20 | 234 || Travis | 2018-08-14 | -20070 || Arthur | 2016-04-01 | 690 || Benjamin | 2014-01-01 | 20394 |+----------+--------------+----------------------------------------------------------+
Note that when comparing a date with a datetime, the date is assumed to have time part '00:00:00'
