MySQL Tutorial/Date Time Functions/TIMESTAMPDIFF — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 09:50, 26 мая 2010

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)