Usage of date_add() and date_format() in MySQL

1. The usage of MySQL's final date_add() function

Demand description :

In the process of using mysql, you need to calculate the date, such as adding days or hours to a certain date.

Under this record, the use of the date_add() function.

Operation process :

date_add() function syntax:

DATE_ADD(date,INTERVAL expr unit)

Note: date_add() and adddate() are synonyms .

Parameter description :

date: start date or start time

expr: specifies an interval value, increase or decrease in the starting time, note : expr is a string. For negative interval, it can start with "-"

unit: indicates a unit, for example, is it 1 day or an hour added.

1. Add n days to a certain date

Copy code
mysql> select date_add('2018-06-26',INTERVAL '5' day);+-----------------------------------------+| date_add('2018-06-26',INTERVAL '5' day) |+-----------------------------------------+| 2018-07-01                              |+-----------------------------------------+1 row in set (0.00 sec) mysql> select date_add('2018-06-26',INTERVAL '-5' day);+------------------------------------------+| date_add('2018-06-26',INTERVAL '-5' day) |+------------------------------------------+| 2018-06-21                               |+------------------------------------------+1 row in set (0.01 sec)
Copy code

Remarks: expr is a string. If you add a positive number of days, you can directly '5'. If it is a subtraction, you can use the date_sub function or expr is a negative value of'-5'.

2. Add n hours, n minutes, and n seconds to a certain date

Copy code
mysql> select date_add('2018-06-26 23:59:59',INTERVAL 1 hour);  #对于日期加上1小时+-------------------------------------------------+| date_add('2018-06-26 23:59:59',INTERVAL 1 hour) |+-------------------------------------------------+| 2018-06-27 00:59:59                             |+-------------------------------------------------+1 row in set (0.00 sec) mysql> select date_add('2018-06-26 23:59:59',INTERVAL 1 minute);  #对于日期加上1分钟+---------------------------------------------------+| date_add('2018-06-26 23:59:59',INTERVAL 1 minute) |+---------------------------------------------------+| 2018-06-27 00:00:59                               |+---------------------------------------------------+1 row in set (0.00 sec) mysql> select date_add('2018-06-26 23:59:59',INTERVAL 1 second);  #对日期加上1秒钟+---------------------------------------------------+| date_add('2018-06-26 23:59:59',INTERVAL 1 second) |+---------------------------------------------------+| 2018-06-27 00:00:00                               |+---------------------------------------------------+1 row in set (0.00 sec)
Copy code

3. Add n minutes and n seconds to a certain date

Copy code
mysql> select date_add('2018-06-26 23:59:59',INTERVAL '1:1' MINUTE_SECOND);+--------------------------------------------------------------+| date_add('2018-06-26 23:59:59',INTERVAL '1:1' MINUTE_SECOND) |+--------------------------------------------------------------+| 2018-06-27 00:01:00                                          |+--------------------------------------------------------------+1 row in set (0.00 sec)
Copy code

Remarks: Add 1 minute and 1 second at a time.

4. Add n hours, n minutes and n seconds to a certain date

Copy code
mysql> select date_add('2018-06-26 23:59:59',INTERVAL '1:1:1' HOUR_SECOND);+--------------------------------------------------------------+| date_add('2018-06-26 23:59:59',INTERVAL '1:1:1' HOUR_SECOND) |+--------------------------------------------------------------+| 2018-06-27 01:01:00                                          |+--------------------------------------------------------------+1 row in set (0.00 sec)
Copy code

Note: The unit HOUR_SECOND is from hours to seconds. expr:HOURS:MINUTES:SECONDS

5. Add n hours and n minutes to a certain date

Copy code
mysql> select date_add('2018-06-26 23:59:59',INTERVAL '1:1' HOUR_MINUTE);+------------------------------------------------------------+| date_add('2018-06-26 23:59:59',INTERVAL '1:1' HOUR_MINUTE) |+------------------------------------------------------------+| 2018-06-27 01:00:59                                        |+------------------------------------------------------------+1 row in set (0.00 sec)
Copy code

Remarks: add 1 hour and 1 minute

6. Add days, hours, minutes, and seconds to a date

Copy code
mysql> select date_add('2018-06-26 23:59:59',INTERVAL '2 2:1:1' DAY_SECOND);+---------------------------------------------------------------+| date_add('2018-06-26 23:59:59',INTERVAL '2 2:1:1' DAY_SECOND) |+---------------------------------------------------------------+| 2018-06-29 02:01:00                                           |+---------------------------------------------------------------+1 row in set (0.00 sec)
Copy code

Remarks: add 2 days, 2 hours, 2 minutes, 1 second to the date

Unit (unit) and expr (expression) comparison table :

Summary :

For the calculation of a certain date, no matter how much you add, you can adjust it according to this table. For date subtraction, try to use the date_sub() function to achieve.

The above is reproduced at: https://www.cnblogs.com/chuanzhang053/p/9228798.html

2. Usage of MySQL's final date_format() function

Definition and usage

The DATE_FORMAT() function is used to display date/time data in different formats.

grammar

DATE_FORMAT(date,format)

The date  parameter is a valid date. format  specifies the date/time output format.

The formats that can be used are:

formatdescription
%aAbbreviated weekday
%bAbbreviated month name
%cMonth, number
%DDay of the month with English prefix
%dDay of month, numeric value (00-31)
%eDay of the month, numeric value (0-31)
%fMicroseconds
%HHour (00-23)
%hHour (01-12)
%IHour (01-12)
%iMinutes, number (00-59)
%jDay of the year (001-366)
%kHour (0-23)
%lHour (1-12)
%MMonth name
%mMonth, number (00-12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss AM or PM)
%SSeconds (00-59)
%sSeconds (00-59)
%TTime, 24-hours (hh:mm:ss)
%UWeek (00-53) Sunday is the first day of the week
%uWeek (00-53) Monday is the first day of the week
%VWeek (01-53) Sunday is the first day of the week, used with %X
%vWeek (01-53) Monday is the first day of the week, used with %x
%WWeekday name
%wDay of the week (0=Sunday, 6=Saturday)
%XYear, where Sunday is the first day of the week, 4 digits, used with %V
%xYear, where Monday is the first day of the week, 4 digits, used with %v
%YYears, 4 digits
%yYear, 2 digits

Instance

The following script uses the DATE_FORMAT() function to display different formats. We use NOW() to get the current date/time:

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')DATE_FORMAT(NOW(),'%m-%d-%Y')DATE_FORMAT(NOW(),'%d %b %y')DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

The result is similar:

Dec 29 2008 11:45 PM12-29-200829 Dec 0829 Dec 2008 16:25:46.635