Detailed explanation of how to operate dates in MySQL

Date is a very important part of database storage. The following summarizes the commonly used date operations in MySQL

1.select curDate(); #Get the current date select curTime(); #Get the current time select now (); #Get the current date + time

2. List an example of adding and subtracting days, the rest can be understood by looking at the English meaning

select date_add(now(), interval 1 day);        #当前日期天数+1
select date_add(now(), interval -1 day);        #当前日期天数-1
select date_add(now(), interval 1 hour);  
select date_add(now(), interval 1 minute);
select date_add(now(), interval 1 second);
select date_add(now(), interval 1 microsecond);
select date_add(now(), interval 1 week);
select date_add(now(), interval 1 month);
select date_add(now(), interval 1 quarter);
select date_add(now(), interval 1 year);

3. Date_sub and date_add function integration are opposite

select date_sub(now(), interval 1 day);        #当前日期天数-select date_sub(now(), interval -1 day);        
#当前日期天数+select date_sub(now(), interval 1 hour); 
select date_sub(now(), interval 1 minute)select date_sub(now(), interval 1 second)select date_sub(now(), interval 1 microsecond)
select date_sub(now(), interval 1 week)select date_sub(now(), interval 1 month)select date_sub(now(), interval 1 quarter)
select date_sub(now(), interval 1 year);

4.datediff function calculates the number of days between two dates

datediff(date1, date2);  #date1 - date2

5.timediff function calculates two time intervals

timediff(time1, time2);  #time1 - time2
time1和time2的格式必须要一样，返回时间差

##6.str_to_date(str, format) Convert string to date

select str_to_date('11/09/2014', '%m/%d/%Y'); -- 2014-11-09

7. There are many more convenient functions, so I won’t list them one by one. I will continue to improve them when they are used

The above is the detailed content of Detailed explanation of how to operate dates in MySQL. For more information, please follow other related articles on the PHP Chinese website!