Shulou(Shulou.com)06/01 Report--

This article mainly introduces mysql how to obtain the current date before and after time, the content of the article is carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand how mysql to obtain the current date before and after the time bar.

Select subdate (curdate (), date_format (curdate (),'% w')-1) / / get the current date on Monday of this week

Select subdate (curdate (), date_format (curdate (),'% w')-7) / / get the current date on Sunday of this week

These two sentences are a method used by mysql to get the current date on Monday or Sunday, so how does this sentence work?

% w represents the number of days of the week in the form of numbers, 0 for Sunday and 6 for Saturday, which is different from our general understanding that a week begins on Monday.

Date_format is a date conversion function

Date_format (curdate (),'% w') indicates that there are several days between the current date and last Sunday, that is, the current date minus the date of last Sunday = the number of days (for example: curdate () is 2011-01-11, then last Sunday is 2011-01-09, minus 2), so if you output this sentence alone: selectdate_format (curdate (),'% w') the result is 2.

In mysqlapi, the subdate function is explained as follows:

SELECTSUBDATE (NOW (), 1) AS'Yesterday'

SUBDATE (NOW (), INTERVAL-1DAY) AS'Tomorrow'

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

The subdate function is to subtract 2 days from the current time, and the time of last Sunday is 2011-01-09.

So if you run selectsubdate (curdate (), date_format (curdate (),'% w')-2), it means that with the current time-(2-2), that is,-0, the result is the current date itself.

What if we change% w into% d?

% d is a double-digit representation of the number of days in the month.

The direct output should be 11, indicating the date of the current month.

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

Put it in the opening statement to subtract 11 from the current time, and the result is 2010-12-31

Selectsubdate ('2011-01-11 dating format (' 2011-01-11 dating% d'))

Mysql database according to the current system time, before and after several seconds, minutes, hours and days

Take the 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 60 second)

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

Take the next minute of the current time:

Select ADDDATE (now (), interval 60 second)

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

By changing the units above. You can take the time of minutes, hours and days.

Take the time of the previous minute:

Select SUBDATE (now (), interval 1 minute)

Output: 2016-06-16 16:16:38

Take the time of the previous hour:

Select SUBDATE (now (), interval 1 hour)

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

Take the time of the previous day:

Select SUBDATE (now (), interval 1 day)

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

# #

One minute after taking:

Select ADDDATE (now (), interval 1 minute)

Output: 2016-06-16 16:17:38

One hour after taking:

Select ADDDATE (now (), interval 1 hour)

Output: 2016-06-16 17:17:38

Take the time of the day after:

Select ADDDATE (now (), interval 1 day)

Output: 2016-06-17 16:17:38

After reading the above about how mysql obtains the current date and time, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.