MySQL Tutorial/Date Time Functions/TIMESTAMPDIFF
Содержание
- 1 SELECT TIMESTAMPDIFF(DAY,"2003-02-01","2003-05-01");
- 2 SELECT TIMESTAMPDIFF(FRAC_SECOND,"2003-02-01","2003-05-01");
- 3 SELECT TIMESTAMPDIFF(HOUR,"2003-02-01","2003-05-01");
- 4 SELECT TIMESTAMPDIFF(MINUTE,"2003-02-01","2003-05-01");
- 5 SELECT TIMESTAMPDIFF(MONTH,"2003-02-01","2003-05-01");
- 6 SELECT TIMESTAMPDIFF(QUARTER,"2003-02-01","2003-05-01");
- 7 SELECT TIMESTAMPDIFF(SECOND,"2003-02-01","2003-05-01");
- 8 SELECT TIMESTAMPDIFF(WEEK,"2003-02-01","2003-05-01");
- 9 SELECT TIMESTAMPDIFF(YEAR,"2002-05-01","2001-01-01");
- 10 SELECT TIMESTAMPDIFF(YEAR,"2003-02-01","2003-05-01");
- 11 TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2): Returns the integer difference between datetime_expr1 and datetime_expr2
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:
- FRAC_SECOND,
- SECOND,
- MINUTE,
- HOUR,
- DAY,
- WEEK,
- MONTH,
- QUARTER, or
- 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>