MySQL Tutorial/Date Time Functions/TIMESTAMPDIFF

SELECT TIMESTAMPDIFF(DAY,"2003-02-01","2003-05-01");

   <source lang="sql">

mysql> mysql> SELECT TIMESTAMPDIFF(DAY,"2003-02-01","2003-05-01"); +----------------------------------------------+ | TIMESTAMPDIFF(DAY,"2003-02-01","2003-05-01") | +----------------------------------------------+ | 89 | +----------------------------------------------+ 1 row in set (0.00 sec) mysql></source>

SELECT TIMESTAMPDIFF(FRAC_SECOND,"2003-02-01","2003-05-01");

   <source lang="sql">

mysql> mysql> SELECT TIMESTAMPDIFF(FRAC_SECOND,"2003-02-01","2003-05-01"); +------------------------------------------------------+ | TIMESTAMPDIFF(FRAC_SECOND,"2003-02-01","2003-05-01") | +------------------------------------------------------+ | 7689600000000 | +------------------------------------------------------+ 1 row in set (0.00 sec)</source>

SELECT TIMESTAMPDIFF(HOUR,"2003-02-01","2003-05-01");

   <source lang="sql">

mysql> mysql> mysql> SELECT TIMESTAMPDIFF(HOUR,"2003-02-01","2003-05-01"); +-----------------------------------------------+ | TIMESTAMPDIFF(HOUR,"2003-02-01","2003-05-01") | +-----------------------------------------------+ | 2136 | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql></source>

SELECT TIMESTAMPDIFF(MINUTE,"2003-02-01","2003-05-01");

   <source lang="sql">

mysql> mysql> SELECT TIMESTAMPDIFF(MINUTE,"2003-02-01","2003-05-01"); +-------------------------------------------------+ | TIMESTAMPDIFF(MINUTE,"2003-02-01","2003-05-01") | +-------------------------------------------------+ | 128160 | +-------------------------------------------------+ 1 row in set (0.00 sec)</source>

SELECT TIMESTAMPDIFF(MONTH,"2003-02-01","2003-05-01");

   <source lang="sql">

mysql> mysql> SELECT TIMESTAMPDIFF(MONTH,"2003-02-01","2003-05-01"); +------------------------------------------------+ | TIMESTAMPDIFF(MONTH,"2003-02-01","2003-05-01") | +------------------------------------------------+ | 3 | +------------------------------------------------+ 1 row in set (0.00 sec) mysql></source>

SELECT TIMESTAMPDIFF(QUARTER,"2003-02-01","2003-05-01");

   <source lang="sql">

mysql> mysql> SELECT TIMESTAMPDIFF(QUARTER,"2003-02-01","2003-05-01"); +--------------------------------------------------+ | TIMESTAMPDIFF(QUARTER,"2003-02-01","2003-05-01") | +--------------------------------------------------+ | 1 | +--------------------------------------------------+ 1 row in set (0.01 sec) mysql></source>

SELECT TIMESTAMPDIFF(SECOND,"2003-02-01","2003-05-01");

   <source lang="sql">

mysql> mysql> SELECT TIMESTAMPDIFF(SECOND,"2003-02-01","2003-05-01"); +-------------------------------------------------+ | TIMESTAMPDIFF(SECOND,"2003-02-01","2003-05-01") | +-------------------------------------------------+ | 7689600 | +-------------------------------------------------+ 1 row in set (0.00 sec) mysql></source>

SELECT TIMESTAMPDIFF(WEEK,"2003-02-01","2003-05-01");

   <source lang="sql">

mysql> mysql> mysql> SELECT TIMESTAMPDIFF(WEEK,"2003-02-01","2003-05-01"); +-----------------------------------------------+ | TIMESTAMPDIFF(WEEK,"2003-02-01","2003-05-01") | +-----------------------------------------------+ | 12 | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql></source>

SELECT TIMESTAMPDIFF(YEAR,"2002-05-01","2001-01-01");

   <source lang="sql">

mysql> mysql> SELECT TIMESTAMPDIFF(YEAR,"2002-05-01","2001-01-01"); +-----------------------------------------------+ | TIMESTAMPDIFF(YEAR,"2002-05-01","2001-01-01") | +-----------------------------------------------+ | -1 | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql></source>

SELECT TIMESTAMPDIFF(YEAR,"2003-02-01","2003-05-01");

   <source lang="sql">

mysql> mysql> SELECT TIMESTAMPDIFF(YEAR,"2003-02-01","2003-05-01"); +-----------------------------------------------+ | TIMESTAMPDIFF(YEAR,"2003-02-01","2003-05-01") | +-----------------------------------------------+ | 0 | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql></source>

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2): Returns the integer difference between datetime_expr1 and datetime_expr2

The unit for the result is given by the unit argument.

The unit should be one of the following values:

1. FRAC_SECOND,
2. SECOND,
3. MINUTE,
4. HOUR,
5. DAY,
6. WEEK,
7. MONTH,
8. QUARTER, or
9. YEAR.

   <source lang="sql">

mysql> mysql> SELECT TIMESTAMPDIFF(MONTH,"2003-02-01","2003-05-01"); +------------------------------------------------+ | TIMESTAMPDIFF(MONTH,"2003-02-01","2003-05-01") | +------------------------------------------------+ | 3 | +------------------------------------------------+ 1 row in set (0.00 sec)</source>