MySQL Tutorial/Date Time Functions/TIMESTAMPDIFF

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

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>

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

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)

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

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>

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

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)

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

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>

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

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>

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

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>

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

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>

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

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>

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

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>

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.

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)