Examples of functions that mysql uses to obtain the date value (previous day, one day, date difference)

Examples of functions that mysql uses to obtain the date value (previous day, one day, date difference)

Examples of functions that mysql uses to obtain the date value.

(1) obtain the current date:

Mysql> SELECT curdate ();

+ ------------ +
| Curdate () |
+ ------------ +
|
+ ------------ +

The following two time functions are equivalent to curtime ():

Current_time ()

Current_time
 
(2) mysql obtains the current date + time:

Mysql> select now ();

+ --------------------- +
| Now () |
+ --------------------- +
| 22:10:40 |
+ --------------------- +

In addition to the now () function, MySQL also has the following functions:

Current_timestamp () current_timestamp

Localtime () localtime

Localtimestamp () localtimestamp

These datetime functions are equivalent to now (). Considering that the now () function is short and easy to remember, we recommend that you always use now () to replace the functions listed above.

In addition, the format of the value obtained by the sysdate () Date and time function is similar to that obtained by now (). The difference is that now () is obtained at the start of execution, and sysdate () obtain the value dynamically when the function is executed.

(3) Previous day:

Mysql> select date_sub (curdate (), interval 1 day );

+ ------------------------------------ +
| Date_sub (curdate (), interval 1 day) |
+ ------------------------------------ +
|
+ ------------------------------------ +
 
It is the day before the current day in parentheses. If the number of days is counted, change '1' in brackets to the corresponding number of days. If you want to calculate the month or year, change day to month or year.

(4) obtain the year of the previous day:

Mysql> select year (DATE_SUB (CURDATE (), INTERVAL 1 DAY ));

+ ------------------------------------------ +
| YEAR (DATE_SUB (CURDATE (), INTERVAL 1 DAY) |
+ ------------------------------------------ +
| 1, 2017 |
+ ------------------------------------------ +

Example of the date_sub () function:

Date_sub ('2017-02-25 ', interval 1 day) indicates 2017

Date_sub ('2017-02-25 ', interval 0 day) indicates 2017

Date_sub ('2017-02-25 ', interval-1 day) indicates 2017

Date_sub ('2017-02-31 ', interval-1 day) indicates 2017

Date_sub (curdate (), interval 1 day) indicates

Date_sub (curdate (), interval-1 day) indicates

Date_sub (curdate (), interval 1 month) indicates 2013-04-20

Date_sub (curdate (), interval-1 month) indicates 2013-06-20

Date_sub (curdate (), interval 1 year) indicates

Date_sub (curdate (), interval-1 year) indicates

(5) The difference value after the date and time subtraction of MySQL.

Datediff (date1, date2), timediff (time1, time2)

MySQL datediff (date1, date2): two dates subtract date1-date2 and return the number of days.

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

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

MySQL timediff (time1, time2): two times minus time1-time2, return the difference between time.

Select timediff ('2017-08-08 08:08:08 ', '2017-08-08 00:00:00'); -- 08:08:08

Select timediff ('08: 08: 08', '00: 00: 00'); -- 08:08:08

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