MySQL gets the time before and after the current date

Selectsubdate (Curdate (), Date_format (Curdate (), '%w ')-1)//Get current date in the Monday of the week

Selectsubdate (Curdate (), Date_format (Curdate (), '%w ')-7)//Get current date on Sunday of the week

These two sentences are a method that MySQL uses to fetch the current date of Monday or Sunday, then how does this sentence work?

%w is a number representing the number of days of the week (0=sunday,1=monday,..., 6=saturday), 0 for Sunday, 6 for Saturday, with our general knowledge that the week starts from Monday.

Date_format is a date conversion function

Date_format (Curdate (), '%w ') represents a few days between the current date and last Sunday, that is, the current date minus last Sunday's date = number of days (example: Curdate () is 2011-01-11, then last week Sunday is 2011-01-09, the difference between the two is 2) so if you output this sentence separately: Selectdate_format (Curdate (), '%w ') The result is 2.

This is explained in Mysqlapi for the Subdate function:

Selectsubdate (Now (), 1) as ' Yesterday ',

Subdate (now (), interval-1day) as ' tomorrow ';

The positive value is yesterday, the negative value is tomorrow, the starting point of the current time is the last Sunday of the current time

The Subdate function is to subtract 2 days from the current time, resulting in Sunday last week for 2011-01-09

So if you run Selectsubdate (Curdate (), Date_format (Curdate (), '%w ')-2, it means that the current time-(2-2), or 0-is the result of the present date itself.

What if I change%w to%d?

%d is a two-digit number of days in the month (00,01,..., 31)

The result of the direct output should be 11, indicating that the current date is the day of the week in one months

Selectdate_format (' 2011-01-11 ', '%d ')

Put in the beginning of the statement is to use the current time minus 11, the result is 2010-12-31

Selectsubdate (' 2011-01-11 ', date_format (' 2011-01-11 ', '%d '))

MySQL database based on the current system time, take a few seconds before and after a few minutes a few hours days

Take Current Time :

Select Current_timestamp;

Output: 2016-06-16 16:12:52

Select Now ();

Output: 2016-06-16 16:12:52

Take one minute before the current time:

Select Subdate (now (), Interval-second);

Output: 2016-06-16 16:11:52

take the current time Next minute:
Select Adddate (now (), Interval-second);

Output: 2016-06-16 16:13:52

By changing the units above. can be taken before and after minutes, hours, days of time

Take one minute before:

Select Subdate (now (), interval 1 minute);

output:2016-06-16 16:16:38

Take one hour before the time:

Select Subdate (now (), interval 1 hour);

output:2016-06-16 15:17:38

Take the day before:

Select Subdate (now (), Interval 1 day);

output:2016-06-15 16:17:38

###########################

Take one minute after the time:

Select Adddate (now (), interval 1 minute);

output:2016-06-16 16:17:38

Take one hour after the time:

Select Adddate (now (), interval 1 hour);

output:2016-06-16 17:17:38

Take the day after the time:

Select Adddate (now (), Interval 1 day);

output:2016-06-17 16:17:38

This article originates from http://www.th7.cn/db/mysql/201606/192064.shtml

This article is from the "Dream to Reality" blog, please be sure to keep this source http://lookingdream.blog.51cto.com/5177800/1916695

MySQL gets the time before and after the current date