Description of Data_ADD and date formatting functions in MySQL

Today I saw the MySQL date function, which is a lot of useful. Here I only stick two parameters that are not easy to remember. (Refer to MySQL 5.1 reference manual)

• DATE_ADD (Date, INTERVALExpr Type) DATE_SUB (Date, INTERVALExpr Type)

These functions perform date operations.DateIs a DATETIME or DATE value, used to specify the start time.ExprIs an expression used to specify the time interval value added or subtracted from the start date.ExprIs a string. For the time interval of negative values, it can start with.TypeIt indicates the expression interpretation method.

Key words INTERVA andTypeThe classifier is case insensitive.

The following table showsTypeAndExprParameter relationship:

Type Value	Expected 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	'Urs. MICROSECONDS'
HOUR_SECOND	'Hours: MINUTES: seconds'
HOUR_MINUTE	'Urs: MINUTES'
DAY_MICROSECOND	'Days. MICROSECONDS'
DAY_SECOND	'Days HOURS: MINUTES: seconds'
DAY_MINUTE	'Days HOURS: MINUTES'
DAY_HOUR	'Days urs'
YEAR_MONTH	'Ears-months'

MySQL allows anyExprFormat. The recommended Delimiter is displayed in the table. IfDateThe parameter is a DATE value, and your calculation only includes the YEAR, MONTH, and DAY sections (that is, there is no time section). The result is a DATE value. Otherwise, the result is a DATETIME value.

If the expression at the other end is a date or datetime value, INTERVALExpr TypeOnly the two ends of the + operator are allowed. For the-operator, INTERVALExpr TypeIt is only allowed on the right side, because it is meaningless to extract a date or date and time value from a time interval. (See the example below ).

Mysql>SELECT '2014-12-31 23:59:59 '+ INTERVAL 1 SECOND;

-> '2017-01-01 00:00:00'

Mysql>Select interval 1 DAY + '2017-12-31 ';

-> '2017-01-01'

Mysql>SELECT '2014-01-01 '-INTERVAL 1 SECOND;

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

Mysql>SELECT DATE_ADD ('2017-12-31 23:59:59 ',

->INTERVAL 1 SECOND );

-> '2017-01-01 00:00:00'

Mysql>SELECT DATE_ADD ('2017-12-31 23:59:59 ',

->INTERVAL 1 DAY );

-> '2017-01-01 23:59:59'

Mysql>SELECT DATE_ADD ('2017-12-31 23:59:59 ',

->INTERVAL '1: 1' MINUTE_SECOND );

-> '2017-01-01 00:01:00'

Mysql>SELECT DATE_SUB ('2017-01-01 00:00:00 ',

->INTERVAL '1 'DAY_SECOND );

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

Mysql>SELECT DATE_ADD ('2017-01-01 00:00:00 ',

->INTERVAL '-1 10' DAY_HOUR );

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

Mysql>SELECT DATE_SUB ('2017-01-02 ', INTERVAL 31 DAY );

-> '2017-12-02'

Mysql>SELECT DATE_ADD ('2017-12-31 23:59:59. 123 ',

->INTERVAL '1. 999999 'SECOND_MICROSECOND );

-> '2017-01-01 00:00:01. 123'

If you specify an excessively short interval value (not includingTypeAll time intervals as expected by the keyword). MySQL assumes that you have saved the leftmost portion of the time interval value. For example, if you specify a DAY_SECOND type,ExprThe value is expected to include the day, hour, minute, and second. If you specify a value similar to '1: 10', MySQL assumes that the day and hour do not exist, then this value indicates minute and second. In other words, '1: 10' DAY_SECOND is interpreted as equivalent to '1: 10' MINUTE_SECOND. This is equivalent to how MySQL interprets the TIME value as the TIME consumed, rather than the daily explanation.

If you add or subtract some time content to a date value, the result is automatically converted to a date and time value:

Mysql>SELECT DATE_ADD ('2014-01-01 ', INTERVAL 1 DAY );

-> '2017-01-02'

Mysql>SELECT DATE_ADD ('2014-01-01 ', INTERVAL 1 HOUR );

-> '2017-01-01 01:00:00'

If you use a date with a severe format error, the result is NULL. If you have added MONTH, YEAR_MONTH, or YEAR, and the date of one day in the result date is greater than the date of the added MONTH, the date is automatically adjusted to the maximum date of the added month:

Mysql>SELECT DATE_ADD ('2014-01-30 ', INTERVAL 1 MONTH );

-> '2017-02-28'

• DATE_FORMAT (Date,Format)

AccordingFormatString ArrangementDateValue format.

The following specifiers are available inFormatString:

Description	Description
%	Abbreviation of workday (Sun .. Sat)
% B	Abbreviated month name (Jan .. Dec)
% C	Month, digit format (0 .. 12)
% D	Date of the month with English suffix (0th, 1st, 2nd, 3rd ,...)
% D	The date of the month, in the numeric format (00 .. 31)
% E	The date of the month, in the numeric format (0 .. 31)
% F	Microseconds (000000 .. 999999)
% H	Hour (00 .. 23)
% H	Hour (01 .. 12)
% I	Hour (01 .. 12)
% I	Minute, digit format (00 .. 59)
% J	Days in a year (001 .. 366)
% K	Hour (0 .. 23)
% L	Hour (1 .. 12)
% M	Month name (January... December)
% M	Month, digit format (00 .. 12)
% P	AM or PM)
% R	Time, in 12-hour format (hour hh: minute mm: seconds ss plus AM or PM)
% S	Seconds (00 .. 59)
% S	Seconds (00 .. 59)
% T	Time, in 24-hour format (hour hh: minute mm: 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), where Sunday is the first day of the week; and % X is used at the same time
% V	Week (01 .. 53), where Monday is the first day of the week; use both % x
% W	Workday name (Sunday... Saturday)
% W	Daily in a week (0 = Sunday .. 6 = Saturday)
% X	The year of the week, in which Sunday is the first day of the week, in the form of a number, 4 digits; and % V at the same time
% X	The year of the week, where Monday is the first day of the week, in the form of a number, 4 digits; used together with % v
% Y	Year, Number Format, 4-digit
% Y	Year, Number Format (2 digits)
%	'%' Text character

All other characters are copied to the result without explanation.

Note that the '%' character must be before the format specified.

The range of month and date specifiers starts from scratch because MySQL allows storage of incomplete dates such as '2017-00-00.

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');

        -> 'Saturday October 1997'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');

        -> '22:23:00'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

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

        -> '4th 97 Sat 04 10 Oct 277'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

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

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

mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');

        -> '1998 52'