Shulou(Shulou.com)05/31 Report--

How to implement date addition and subtraction in mysql? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

1. MySQL adds a time interval to the date: date_add ()

Set @ dt = now ()

Select date_add (@ dt, interval 1 day);-plus 1 day

Select date_add (@ dt, interval 1 hour);-plus 1 hour

Select date_add (@ dt, interval 1 minute);-add 1 minute

Select date_add (@ dt, interval 1 second);-add 1 second

Select date_add (@ dt, interval 1 microsecond);-add 1 Ms

Select date_add (@ dt, interval 1 week);-plus 1 week

Select date_add (@ dt, interval 1 month);-plus one month

Select date_add (@ dt, interval 1 quarter);-plus 1 season

Select date_add (@ dt, interval 1 year);-plus 1 year

The MySQL adddate (), addtime () function can be replaced by date_add (). Here is an example of how date_add () implements the addtime () function:

> set @ dt = '2009-09-09 12 Suzhou 1233'

Mysql >

Mysql > select date_add (@ dt, interval'01 dt 15 hour_second);-plus 1 hour, 15 minutes and 30 seconds

Date_add (@ dt, interval'01, interval'01, 15 hour_second)

Result: 2009-09-09 13:28:03

Mysql > select date_add (@ dt, interval'01 dt 1515 day_second);-plus 1 hour 15 minutes 30 seconds a day

Date_add (@ dt, interval'1 01GV 1515 day_second)

2008-08-10 13:28:03

Date_add () function, added "1 hour 15 minutes 30 seconds" and "1 day 1 hour 15 minutes 30 seconds" to @ dt respectively

2. MySQL subtracts a time interval from the date: date_sub ()

Mysql > select date_sub ('1998-01-01 00 day_second, interval' 1 1)

Date_sub ('1998-01-01 00 day_second, interval' 1)

Www.2cto.com

The MySQL date_sub () date-time function is the same as date_add (), so I won't repeat it. In addition, there are two functions in MySQL, subdate () and subtime (), which are recommended to be replaced by date_sub ().

3. MySQL alternative date function: period_add (PMagne N), period_diff (P1MaginP2)

The function parameter "P" is formatted as "YYYYMM" or "YYMM", and the second parameter "N" adds or subtracts N month (month).

MySQL period_add (N): date plus / minus N month.

Mysql > select period_add (200808jue 2), period_add (20080808mai Murray 2)

| | period_add (200808jue 2) | period_add (20080808mai2) | |

Result | 200810 | 20080806 |

MySQL period_diff (P1Magol P2): date P1-P2, return N months.

Mysql > select period_diff (200808, 200801)

Period_diff (200808, 200801)

Results: 7

In MySQL, these two date functions are rarely used.

4. MySQL date and time subtraction function: datediff (date1,date2), timediff (time1,time2)

MySQL datediff (date1,date2): subtracts date1 date2 from two dates and returns the number of days. Website making and learning network arrangement

Select datediff ('2008-08-08,' 2008-08-01');-7

Select datediff ('2008-08-01,' 2008-08-08);-7

MySQL timediff (time1,time2): subtracts time1 time2 from two dates and returns the time difference.

Select timediff ('2008-08-08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08);-08:08:08

Select timediff ('08rig 08 08mm,' 001R 00R 00');-08:08:08

Note: the two argument types of the timediff (time1,time2) function must be the same.

This is the answer to the question about how to add and subtract the date in mysql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.