Mysql time and date addition and subtraction implementation

I will share some date operations in mysql, that is, the addition or subtraction of mysql time and date that we often use. This mysql also has its own function, for more information, see.

The simplest method

The Code is as follows:	Copy code
Select TO_DAYS (str_to_date ('2016/12/1 12:00:00 AM ',' % m/% d/% y '))- TO_DAYS (str_to_date ('2016/11/28 12:00:00 AM ',' % m/% d/% y') as a from table1

Number of days

SELECT TO_DAYS ('2017-02-01)-TO_DAYS ('2017-01-01 ')

The following is a description in the mysql reference manual.

MySQL adds a time interval for the date: date_add ()

The Code is as follows:

Copy code

Set @ dt = now (); Select date_add (@ dt, interval 1 day); -- add 1 day Select date_add (@ dt, interval 1 hour); -- add 1 hour Select date_add (@ dt, interval 1 minute );--... Select date_add (@ dt, interval 1 second ); Select date_add (@ dt, interval 1 microsecond ); Select date_add (@ dt, interval 1 week ); Select date_add (@ dt, interval 1 month ); Select date_add (@ dt, interval 1 quarter ); Select date_add (@ dt, interval 1 year ); Select date_add (@ dt, interval-1 day); -- sub 1 day MySQL adddate () and addtime () functions can be replaced by date_add. The following is an example of addtime () implemented by date_add: Mysql> set @ dt = '2017-08-09 12:12:33 '; Mysql> Mysql> select date_add (@ dt, interval '01: 15: 30 'hour_second ); + ------------------------------------------------ + | Date_add (@ dt, interval '01: 15: 30 'hour_second) | + ------------------------------------------------ + | 13:28:03 | + ------------------------------------------------ + Mysql> select date_add (@ dt, interval '1 01:15:30 'day_second ); + ------------------------------------------------- + | Date_add (@ dt, interval '1 01:15:30 'day_second) | + ------------------------------------------------- + | 13:28:03 | + ------------------------------------------------- +

The date_add () function adds "1 hour 15 minutes 30 seconds" and "1 day 1 hour 15 minutes 30 seconds" to @ dt respectively ". Suggestion: always use the date_add () Date and Time Function to replace adddate () and addtime ().
2. MySQL is a date minus a time interval: date_sub ()

The Code is as follows:	Copy code
Mysql> select date_sub ('2017-01-01 00:00:00 ', interval '1' day_second ); + ---------------------------------------------------------------- + | Date_sub ('2017-01-01 00:00:00 ', interval '1' day_second) | + ---------------------------------------------------------------- + | 22:58:59 |

+ ---------------------------------------------------------------- +