Mysql's Data_add function and date format function Description _mysql

• Date_add (date, INTERVAL expr type) date_sub (date, INTERVAL expr type )

These functions perform date operations. Date is a DATETIME or date value that specifies the starting time.  expr is an expression that specifies the value of a time interval that is added or subtracted from the start date. Expr is a string; For a negative time interval, it can begin with a '-'. type is the keyword, which indicates how the expression is interpreted.

Keyword Interva and type classifiers are case-insensitive.

The following table shows the relationship between the type and the expr parameters:

type value	expected to Expr format
Microsecond	microseconds
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
Day	Days
WEEK	WEEKS
MONTH	MONTHS
Quarter	Quarters
Year	YEARS
Second_microsecond	' SECONDS. Microseconds '
Minute_microsecond	' MINUTES. Microseconds '
Minute_second	' Minutes:seconds '
Hour_microsecond	' HOURS. Microseconds '
Hour_second	' HOURS:MINUTES:SECONDS '
Hour_minute	' Hours:minutes '
Day_microsecond	' Days. Microseconds '
Day_second	' Days HOURS:MINUTES:SECONDS '
Day_minute	' Days Hours:minutes '
Day_hour	' Days HOURS '
Year_month	' Years-months '

MySQL allows punctuation separators in any expr format. The suggested separator is shown in the table. If the date parameter is a date value and your calculation only includes 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 only allowed at both ends of the + operator. For the-operator, the INTERVAL expr type is only allowed at the right end because it is meaningless to extract a date or datetime value from one time interval. (see the example below).

mysql> SELECT ' 1997-12-31 23:59:59 ' + INTERVAL 1 SECOND;

-> ' 1998-01-01 00:00:00 '

mysql> SELECT INTERVAL 1 day + ' 1997-12-31 ';

-> ' 1998-01-01 '

mysql> SELECT ' 1998-01-01 '-INTERVAL 1 SECOND;

-> ' 1997-12-31 23:59:59 '

Mysql> SELECT date_add (' 1997-12-31 23:59:59 ',

-> INTERVAL 1 SECOND);

-> ' 1998-01-01 00:00:00 '

Mysql> SELECT date_add (' 1997-12-31 23:59:59 ',

-> INTERVAL 1 day);

-> ' 1998-01-01 23:59:59 '

Mysql> SELECT date_add (' 1997-12-31 23:59:59 ',

-> INTERVAL ' 1:1 ' minute_second);

-> ' 1998-01-01 00:01:00 '

Mysql> SELECT date_sub (' 1998-01-01 00:00:00 ',

-> INTERVAL ' 1 1:1:1 ' Day_second);

-> ' 1997-12-30 22:58:59 '

Mysql> SELECT date_add (' 1998-01-01 00:00:00 ',

-> INTERVAL '-1 ' day_hour);

-> ' 1997-12-30 14:00:00 '

Mysql> SELECT date_sub (' 1998-01-02 ', INTERVAL Day);

-> ' 1997-12-02 '

Mysql> SELECT date_add (' 1992-12-31 23:59:59.000002 ',

-> INTERVAL ' 1.999999 ' Second_microsecond);

-> ' 1993-01-01 00:00:01.000001 '

If you specify a time interval value that is too short (excluding all the interval parts expected by the type keyword), MySQL assumes that you have omitted the leftmost portion of the time interval value. For example, you specify a type of Day_second, and the value of expr is expected to have the day, hour, minute, and second portions. If you specify a value similar to ' 1:10 ', MySQL assumes that the day and hour portions do not exist, then this value represents minutes and seconds. In other words, ' 1:10 ' day_second is interpreted as equivalent to ' 1:10 ' Minute_second. This is equivalent to the way MySQL interprets a time value as a time-consuming rather than a day.

If you add or subtract some content that contains a time portion to a date value, the result is automatically converted to a date-time value:

Mysql> SELECT date_add (' 1999-01-01 ', INTERVAL 1 day);

-> ' 1999-01-02 '

Mysql> SELECT date_add (' 1999-01-01 ', INTERVAL 1 HOUR);

-> ' 1999-01-01 01:00:00 '

If you use a date with a badly formatted error, the result is NULL. If you add MONTH, Year_month, or year, and the date of the resulting date is greater than the date of the added month, this date is automatically adjusted to the maximum date of the month added:

Mysql> SELECT date_add (' 1998-01-30 ', INTERVAL 1 MONTH);

-> ' 1998-02-28 '

• Date_format (date,FORMAT)

Arranges the format of date values based on the format string.

The following specifiers are available in the format string:

Specifiers	Description
%a	Abbreviated name for weekday (Sun ... Sat)
%b	The abbreviated name of the month. DEC)
%c	month, digital form (0..12)
%d	The month date with the English suffix (0th, 1st, 2nd, 3rd, ...)
%d	Date of the month, digital form (00..31)
%e	Date of the month, digital form (0..31)
%f	Microsecond (000000..999999)
%H	Hours (00..23)
%h	Hours (01..12)
%I	Hours (01..12)
%i	Minutes, digital form (00..59)
%j	Number of days in a year (001..366)
%k	Hours (0..23)
%l	Hours (1..12)
%m	Month name (January. December)
%m	month, digital form (00..12)
%p	A.M. (AM) or PM (PM)
%r	Time, 12-hour system (hours hh: minutes mm: Seconds of SS plus am or PM)
%s	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour system (hours hh: minutes mm: Number of seconds ss)
%u	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), of which Sunday was the first day of the week; Use at the same time as%x
%v	Week (01..53), of which Monday was the first day of the week; Use at the same time as%x
%w	Weekday name (Sunday. Saturday
%w	Daily of the week (0= Sunday. 6= Saturday)
%x	The year of the week, of which Sunday is the first day of the week, the number form, 4 digits; and%v use simultaneously.
%x	The year of the week, of which Monday is the first day of the week, the number form, 4 digits; and%v use simultaneously.
%Y	Year, digital form, 4 digits
%y	Year, number form (2 digits)
%%	'% ' literal characters

All other characters are copied to the result without explanation.

Note that the '% ' character is required before the format specifier.

The range of month and date specifiers starts at zero because MySQL allows you to store incomplete dates such as ' 2004-00-00 '.

SELECT date_format (' 1997-10-04 22:23:00 ', '%w%m%Y ');

-> ' Saturday October 1997 '

SELECT date_format (' 1997-10-04 22:23:00 ', '%h:%i:%s ');

-> ' 22:23:00 '

SELECT date_format (' 1997-10-04 22:23:00 ',

'%d%y%a%d%m%b%j ');

-> ' 4th Sat Oct 277 '

SELECT date_format (' 1997-10-04 22:23:00 ',

'%H%k%I%r%T%s%w ');

-> ' 10:23:00 PM 22:23:00 00 6 '

SELECT date_format (' 1999-01-01 ', '%x%V ');

-> ' 1998 52 '