What is the use of the Date_add () function in MySQL?

Requirements Description :

In the process of using MySQL, you need to calculate the date, for example, a date plus a few days, a few hours, etc.

In 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 that increases or decreases in the starting time, Note that expr is a string. For negative intervals, you can start with "-"

Unit: Represents a unit, for example, plus 1 days or one hours.

1. Add n days to a date

Mysql> SelectDate_add ('2018-06-26', INTERVAL'5'  Day);+-----------------------------------------+|Date_add ('2018-06-26', INTERVAL'5'  Day)|+-----------------------------------------+| 2018- -- on                              |+-----------------------------------------+1Rowinch Set(0.00sec) MySQL> SelectDate_add ('2018-06-26', INTERVAL'-5'  Day);+------------------------------------------+|Date_add ('2018-06-26', INTERVAL'-5'  Day)|+------------------------------------------+| 2018- .- +                               |+------------------------------------------+1Rowinch Set(0.01Sec

Note: Expr is a string, if you add a positive number of days, directly ' 5 ', if it is minus can be used date_sub function or expr as negative '-5 '.

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

Mysql> SelectDate_add ('2018-06-26 23:59:59', INTERVAL1hour); #对于日期加上1小时+-------------------------------------------------+|Date_add ('2018-06-26 23:59:59', INTERVAL1Hour|+-------------------------------------------------+| 2018- .- - xx: -: -                             |+-------------------------------------------------+1Rowinch Set(0.00sec) MySQL> SelectDate_add ('2018-06-26 23:59:59', INTERVAL1minute); #对于日期加上1分钟+---------------------------------------------------+|Date_add ('2018-06-26 23:59:59', INTERVAL1Minute|+---------------------------------------------------+| 2018- .- - xx:xx: -                               |+---------------------------------------------------+1Rowinch Set(0.00sec) MySQL> SelectDate_add ('2018-06-26 23:59:59', INTERVAL1second); #对日期加上1秒钟+---------------------------------------------------+|Date_add ('2018-06-26 23:59:59', INTERVAL1Second|+---------------------------------------------------+| 2018- .- - xx:xx:xx                               |+---------------------------------------------------+1Rowinch Set(0.00Sec

3. Add n minutes N seconds for a date

Mysql> SelectDate_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- .- - xx: on:xx                                          |+--------------------------------------------------------------+1Rowinch Set(0.00Sec

Note: Add 1 minutes, one second at a time.

4. For a date plus n hours n minutes n seconds

Mysql> SelectDate_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- .- -  on: on:xx                                          |+--------------------------------------------------------------+1Rowinch Set(0.00Sec

Note: Unit Hour_second is from hours to seconds. Expr:HOURS:MINUTES:SECONDS

5. Add n hours N minutes to a date

Mysql> SelectDate_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- .- -  on:xx: -                                        |+------------------------------------------------------------+1Rowinch Set(0.00Sec

Remark: Add 1 hours 1 minutes

6. Add a day, a few hours, a few minutes, a few seconds.

Mysql> SelectDate_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- .- in  Geneva: on:xx                                           |+---------------------------------------------------------------+1Rowinch Set(0.00Sec

Note: Add 2 days, 2 hours, 2 minutes and 1 seconds to the date

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

Summary :

For a date calculation, no matter how much, it can be adjusted according to this table. For date subtraction, try using the Date_sub () function.

Document creation time: June 26, 2018 14:01:08

What is the use of the Date_add () function in MySQL?