Mysql specifies the date range extraction method

In the process of database operation, it is inevitable to deal with dates, such as summarizing some indicators by date, counting the total amount within a certain period of time, etc.

If it is a fixed date, it is fine and you can just specify it directly, but in many cases it needs to adapt to the current date. For example: extract data from last Monday to last Sunday, extract data from last month, extract data from the previous N months. . .

What all these requirements have in common is that they depend on the current date! Then, we need to first obtain some information about the current date, such as the current date is the number of this week, the number of this month, etc., and then we can proceed to the next step.

1. Before extracting the required date range, let's introduce several commonly used functions

-- Run this sentence first SET @t = '2018-07-26 11:41:29';
-- Run this sentence again SELECT DATE(@t) current date, YEAR(@t) year, MONTH(@t) month, WEEK(@t) week number of this year, DAY(@t) day number of this month, 
HOUR(@t) hours, MINUTE(@t) minutes, SECOND(@t) seconds

When running it, the result is as follows:

2. Here are some commonly used date addition and subtraction functions

1. ADDDATE(expr, days) / SUBDATE(expr, days):

The ADDDAT function has two parameters. The first is the base date, which is the date to be calculated. The second is an interval expression, such as: INTERVAL 1 HOUR, where INTERVAL means interval, the middle number 1 can be replaced by any integer, and the third hour can be replaced by time units such as day/month/year.

The same is true for SUBDATE, except that it becomes a subtraction operation.

The complete usage is as follows:

SELECT ADDDATE('2018-07-26 11:41:29',INTERVAL 1 HOUR);
SELECT SUBDATE('2018-07-26 11:41:29',INTERVAL 1 HOUR);

2. DATE_ADD() / DATE_SUB():

The usage is the same as ADDDATE(expr, days) / SUBDATE(expr, days).

3. Date range interception

Next, using the date functions introduced above, you can intercept the date range.

1. Last week

-- Extract the date range of last week SELECT CURDATE() NOW,
ADDDATE(ADDDATE(DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 1 DAY),-6),0) startdate,
ADDDATE(DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 1 DAY),0) duetodate

2. Last month

-- SELECT CURDATE() NOW for last month, 
DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY), INTERVAL -1 MONTH) startdate,
DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE()) DAY), INTERVAL 0 MONTH) duetodate

3. First four months

-- First four monthsSELECT CURDATE() NOW, 
ADDDATE(ADDDATE(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY), INTERVAL -4 MONTH) startdate,
DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE()) DAY), INTERVAL 0 MONTH) duetodate

4. Last Thursday to this Wednesday

Sometimes statistics are not collected according to natural weeks, and you need to customize the start and end dates of a week. You can do this as follows:

SELECT ADDDATE(DATE_SUB(CURDATE(),INTERVAL (IF(WEEKDAY(CURDATE())IN(3,4,5,6),WEEKDAY(CURDATE()),WEEKDAY(CURDATE())+7 )) + 1 DAY),-3) startdate
, ADDDATE(DATE_SUB(CURDATE(),INTERVAL (IF(WEEKDAY(CURDATE())IN(3,4,5,6),WEEKDAY(CURDATE()),WEEKDAY(CURDATE())+7 )) + 1 DAY),3)duetodate

OK, now you can extract time and date information and intercept any interval.

Summarize

The above is the method of extracting the specified date range in Mysql introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!