Detailed explanation of how to operate dates in MySQL-Mysql Tutorial-php.cn

Detailed explanation of how to operate dates in MySQL

黄舟

Release： 2017-08-01 11:16:13

Original

1716 people have browsed it

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

Detailed explanation of how to operate dates in MySQL

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);

Copy after login

Detailed explanation of how to operate dates in MySQL

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);

Copy after login

Detailed explanation of how to operate dates in MySQL

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

datediff(date1, date2);  #date1 - date2

Copy after login

Detailed explanation of how to operate dates in MySQL

5.timediff function calculates two time intervals

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

Copy after login

Detailed explanation of how to operate dates in MySQL

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

select str_to_date(&#39;11/09/2014&#39;, &#39;%m/%d/%Y&#39;); -- 2014-11-09

Copy after login

Detailed explanation of how to operate dates in MySQL

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!