The easiest way to do it
The code is as follows |
Copy Code |
Select To_days (str_to_date (' 12/1/2001 12:00:00 AM ', '%m/%d/%y '))- To_days (Str_to_date (' 11/28/2001 12:00:00 AM ', '%m/%d/%y ')) as a from table1 |
Number of days to arrive
SELECT to_days (' 1999-02-01)-to_days (' 1999-01-01 ')
The following is a description of 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 (), Addtime () function, can be replaced with Date_add (). The following is an example of the Date_add () Implementation addtime () feature: mysql> Set @dt = ' 2008-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) | +------------------------------------------------+ | 2008-08-09 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) | +-------------------------------------------------+ | 2008-08-10 13:28:03 | +-------------------------------------------------+ |
The Date_add () function adds "1 hours 15 minutes 30 seconds" and "1 days 1 hours 15 minutes 30 seconds" to the @dt respectively. Recommendation: Always use the Date_add () Date time function to replace adddate (), Addtime ().
2. MySQL subtracts a time interval from the date: Date_sub ()
The code is as follows |
Copy Code |
Mysql> Select Date_sub (' 1998-01-01 00:00:00 ', interval ' 1 1:1:1 ' Day_second); +----------------------------------------------------------------+ | Date_sub (' 1998-01-01 00:00:00 ', interval ' 1 1:1:1 ' Day_second) | +----------------------------------------------------------------+ | 1997-12-30 22:58:59 | |
+----------------------------------------------------------------+