MySQL Common date function (reprint)

/*
Date_add (Date,interval expr type) and date_sub (Date,interval expr type) perform date operations. Date is a datetime or date value used to specify the start time. Expr is an expression that specifies the time interval value to be added or subtracted from the start date. Expr is a string, and for a negative time interval, it can begin with a '-'. Type is a keyword that indicates how the expression is interpreted.
Keyword Interva and type classifiers are case insensitive.

Date manipulation, adddate is synonymous with date_add. If Adddate does not specify interval, the number of days is the default as interval (Date_add not)
*/
Select Date_add (' 2011-01-01 ', Interval), Date_add (' 2011-01-01 ', Interval-1 Day), Adddate (' 2011-01-01 ', interval Adddate (' 2011-01-01 ', '), Date_sub (' 2011-01-01 ', Interval 1 day), Subdate (' 2011-01-01 ', Interval 1 day);

--Time operation
Select Addtime (' 2011-01-01 00:00:00 ', ' 1:05:33 '), Addtime (' 00:00:00 ', ' 1:05:33 ');

--Get the current date, +0 will remove the split line, current_date and curdate are synonyms
Select Curdate (), Curdate () +0,current_date ();

--Get the current time, curtime and current_time are synonyms
Select Curtime (), Current_time (), Current_time () +0;

--Queries the current server time zone and the currently connected time zone, and the current connected time zone has the same value as the global variable Time_zone, but can be reset with the following statement: Set time_zone = timezone;
SELECT @ @global. time_zone, @ @session. Time_zone;

--Specify the time zone conversion
Select Convert_tz (' 2004-01-01-12:00:00 ', ' +08:00 ', ' +00:00 ');

--Get the current date and time, Current_timestamp and now are synonyms
Select Current_timestamp (), now (), now () +0,current_timestamp () +0;

--Extracts the date part in the date or time-date expression expr.
Select Date (' 2003-12-31 01:02:03 ');

--DateDiff (EXPR,EXPR), returns the number of days between expr minus expr2. Expr and expr2 are either date or date-and-time expressions. Only the date parts of these values are used in the calculation.
Select DateDiff (' 1997-12-31 23:59:59 ', ' 1997-12-30 '), DateDiff (' 1997-11-30 23:59:59 ', ' 1997-12-31 ');

/*
MySQL allows punctuation separators in any expr format. The suggested delimiter is shown in the table. If the date parameter is a date value, and your calculation includes only the year, month, and day portions (that is, there is no time part), the result is a date value. Otherwise, the result will be a datetime value.

If the expression at the other end is a date or datetime value, the interval expr type is allowed only at both ends of the + operator. For the-operator, the interval expr type is only allowed on its right side, because extracting a date or datetime value from a time interval is meaningless. (see the example below).
*/
Select ' 1997-12-31 ' + interval 1 second, ' 1997-12-31 '-interval 1 second, ' 1997-12-31 23:59:59 ' + interval 1 second,interv Al 1 day + ' 1997-12-31 ', ' 1998-01-01 '-interval 1 second;

--interval can also use negative values
Select ' 1997-12-31 ' + Interval-1 day;

--Time Date Format function date_format (Date,format)
Select Date_format (now (), '%y-%m-%d%h:%i:%s ');

--a function used to get a part of a date
Select Day (now ()), Dayname (today ()), DayOfWeek (now ()), DayOfYear (now ())

--Extract (type from date)
The time interval type used by the extract () function describes the same as descriptor Date_add () or date_sub (), but extracts its part from the date instead of performing a date operation.
--Get month, number of days, less than 10 do not add 0
Select extract (Year from ' 1999-07-02 '), extract (month from ' 1999-07-02 '); --Convert time to timestamp select Unix_timestamp (' 2009-10-26 10-06-07 ') If the argument is empty, the processing is the current time--converts the timestamp to the time select From_unixtime (1256540102)

MySQL Common date function (reprinted)