MySQL date plus minus

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)

Result: 2009-09-09 13:28:03

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)

Www.2cto.com

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. Web site Production Learning Network finishing

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.

From: http://www.2cto.com/database/201110/107788.html

Note: This time the main use of 4. If you write only Date1-date2, the return is a long shape, such as: 2014-10-29-2014-10-31 =-200000

is not the number of seconds (2 days =172800s), the specific meaning, please the great God to give guidance

MySQL date plus minus