1. MySQL adds a time interval for the date: Date_add ()
Set @dt = Now ();
Select Date_add (@dt, Interval 1 day); -Plus 1 days
Select Date_add (@dt, interval 1 hour); -Plus 1 hours
Select Date_add (@dt, interval 1 minute); -Plus 1 minutes
Select Date_add (@dt, Interval 1 second);-plus 1 seconds
Select Date_add (@dt, interval 1 microsecond);-plus 1 milliseconds
Select Date_add (@dt, Interval 1 week);-Plus 1 weeks
Select Date_add (@dt, interval 1 month);-Plus January
Select Date_add (@dt, interval 1 quarter);-Plus 1 seasons
Select Date_add (@dt, interval 1 year);-Plus 1 years
MySQL adddate (), Addtime () function, can be replaced with Date_add (). The following is an example of the date_add () implementation of the Addtime () feature:
mysql> Set @dt = ' 2009-09-09 12:12:33 ';
mysql>
mysql> Select Date_add (@dt, Interval ' 01:15:30 ' Hour_second);-plus 1 hours, 15 minutes, 30 seconds.
Date_add (@dt, Interval ' 01:15:30 ' Hour_second)
mysql> Select Date_add (@dt, Interval ' 1 01:15:30 ' Day_second);-Add 1 days, 1 hours, 15 minutes, 30 seconds.
Date_add (@dt, Interval ' 1 01:15:30 ' Day_second)
2008-08-10 13:28:03
The Date_add () function adds "1 hours, 15 minutes, 30 seconds" and "1 days, 1 hours, 15 minutes, 30 seconds" for @dt respectively.
2. MySQL for date minus one time interval: date_sub ()
mysql> Select Date_sub (' 1998-01-01 00:00:00 ', interval ' 1 1:1:1 ' Day_second);
date_sub (' 1998-01-01 00:00:00 ', interval ' 1 1:1:1 ' Day_second)
1997-12-30 22:58:59//www.forasp.cn finishing
MySQL date_sub () datetime functions and Date_add () are used in a consistent, no-repeat. In addition, MySQL also has two functions subdate (), Subtime (), recommended, with Date_sub () to replace.
3. MySQL Alternative Date function: Period_add (p,n), Period_diff (P1,P2)
The format of the function parameter "P" is "yyyymm" or "yymm", and the second parameter "n" means increment or subtract N month (month).
MySQL Period_add (p,n): Date plus/minus N months.
mysql> Select Period_add (200808,2), Period_add (20080808,-2)
| period_add (200808,2) | period_add (20080808,-2) |
Results | 200810 | 20080806 |
MySQL Period_diff (P1,P2): Date p1-p2, returns N months.
mysql> Select Period_diff (200808, 200801);
Period_diff (200808, 200801)
Results: 7
In MySQL, these two date functions are rarely used under normal circumstances.
4. MySQL date, time subtraction function: DateDiff (DATE1,DATE2), Timediff (time1,time2)
MySQL DateDiff (date1,date2): two date subtraction Date1 date2, returns the number of days.
Select DateDiff (' 2008-08-08 ', ' 2008-08-01 ');-7
Select DateDiff (' 2008-08-01 ', ' 2008-08-08 ');-7
MySQL Timediff (time1,time2): two date subtraction time1 time2, returns the time difference value.
Select Timediff (' 2008-08-08 08:08:08 ', ' 2008-08-08 00:00:00 ');-08:08:08
Select Timediff (' 08:08:08 ', ' 00:00:00 '); -08:08:08
Note: The Timediff (time1,time2) function must have the same two parameter types.
Copyright NOTICE: This article for Bo Master http://www.zuiniusn.com original article, without Bo Master permission not reproduced.
MySQL Add and subtract time-function-time plus minus
