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