MySQL date plus minus

One, MySQL adds a time interval for the date: Date_add ().

1. Example:

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

2, MySQL adddate (), Addtime () function, can be replaced with Date_add ().

Second, MySQL is the date minus one time interval: Date_sub ():

1, 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

2, MySQL also has two functions subdate (), Subtime (), recommended, with Date_sub () to replace.

Third, 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).

1, 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

2, MySQL, these two date functions, under normal circumstances rarely used.

Four, 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.

V. Several functions of the current time:

1, now (); The current specific date and time,

2, Curdate (); Current date

3, Curtime (); Current time

4, date (date); Extract date part of date or date/time expression

5, Date_add (DATE, INTERVAL expr type); Adds a specified time interval to a date,

Expr table time interval, the type parameter can be the following value:

Microsecond SECOND MINUTE HOUR Day WEEK MONTH QUARTER Year

Second_microsecond Minute_microsecond Minute_second

Hour_microsecond Hour_second Hour_minute Day_microsecond

Day_second Day_minute Day_hour Year_month

6, Date_sub (DATE, INTERVAL expr type); The specified time interval is sent from the date.

The expr parameter is the interval you want to add, the type parameter is the same as Date_add ().

7, DATEDIFF (Date1, date2); Returns the number of days between two dates, with only the date portion of the value participating in the calculation.

8, Date_format (DATE, FORMAT); Displays the date/time in a different format.

format Specifies the date/time in which the output is formatted. The format is:

Format

Describe

Format

Describe

%a

Abbreviated Week name

%p

AM or PM

%b

Abbreviated month name

%r

Time, 12-hour (Hh:mm:ss AM or PM)

%c

Month, value

%s

Seconds (00-59)

%d

Day of the month with English prefixes

%s

Seconds (00-59)

%d

Day of the month, value (00-31)

%T

Time, 24-hour (HH:MM:SS)

%e

Day of the month, value (0-31)

%u

Week (00-53) Sunday is the first day of the week

%f

Microseconds

%u

Week (00-53) Monday is the first day of the week

%H

Hours (00-23)

%V

Week (01-53) Sunday is the first day of the week, with%x

%h

Hours (01-12)

%v

Week (01-53) Monday is the first day of the week, with%x

%I

Hours (01-12)

%W

Week name

%i

Minutes, value (00-59)

%w

Day of the week (0= Sunday, 6 = Saturday)

%j

Days of the Year (001-366)

%x

year, of which Sunday was the first day of the week, 4-bit, with%V used

%k

Hours (0-23)

%x

year, of which Monday was the first day of the week, 4-bit, with%v used

%l

Hours (1-12)

%Y

Year, 4 guests

%M

Month Name

%y

Year, 2 guests

%m

month, value (00-12)

Note:

UPDATE ' deadline ' SET ' end_time ' =date_add (' End_time ', Interval 1 month) where ' Function_type ' =1 plus one months

MySQL date plus minus