Usage of MySQL Timestampdiff and Timestampadd

These two functions, Timestampdiff and timestampadd, are often used when applying.

One, Timestampdiff

Grammar:

Timestampdiff (INTERVAL,DATETIME_EXPR1,DATETIME_EXPR2).

Description

Returns an integer difference between a date or datetime expression Datetime_expr1 and datetime_expr2the. The unit of the result is given by the interval parameter. The parameter must be one of the following values:

1. Frac_second. Indicates that the interval is milliseconds
2. SECOND. Seconds
3. MINUTE. Minutes
4. HOUR. Hours
5. Day. Days
6. WEEK. Week
7. MONTH. Month
8. QUARTER. Quarter
9. Year. Years

Use the following: [SQL]View PlainCopy

2. mysql> Select Timestampdiff (Day,' 2012-08-24 ',' 2012-08-30 ');
4. +----------------------------------------------+  
6. |  Timestampdiff (Day,' 2012-08-24 ',' 2012-08-30 ') |
8. +----------------------------------------------+  
10. | 6 |
12. +----------------------------------------------+  
14. 1 row in Set (0.00 sec)

[SQL]View PlainCopy

2. mysql> Select Timestampdiff (MINUTE,' 2012-08-24 09:00:00 ',' 2012-08-30 12:00:00 ');
4. +-------------------------------------------------------------------+  
6. |  Timestampdiff (MINUTE,' 2012-08-24 09:00:00 ',' 2012-08-30 12:00:00 ') |
8. +-------------------------------------------------------------------+  
10. | 8820 |
12. +-------------------------------------------------------------------+  
14. 1 row in Set (0.01 sec)

Two, Timestampadd syntax: Timestampadd (interval,int_expr,datetime_expr) Description: Adds an integer expression int_expr to a date or datetime expression datetime_expr. The interval in the formula are the same as the values listed above. [SQL]View PlainCopy

2. mysql> Select Timestampadd (minute,8820,' 2012-08-24 09:00:00 ');
4. +-------------------------------------------------+  
6. |  Timestampadd (minute,8820,' 2012-08-24 09:00:00 ') |
8. +-------------------------------------------------+  
10. | 2012-08-30 12:00:00 |
12. +-------------------------------------------------+  
14. 1 row in Set (0.00 sec)

Usage of MySQL Timestampdiff and Timestampadd