How to extract a specified date range from Mysql
In this issue, Xiaobian will bring you about how to extract the specified date interval in Mysql. The article is rich in content and analyzes and narrates from a professional perspective. After reading this article, I hope you can gain something.
First, before extracting the required date interval, we will introduce several commonly used functions.
--Run this sentence SET @t = '2018-07-26 11:41: 29';--Run this sentence SELECT DATE(@t) Current Date,YEAR(@t) Year, MONTH(@t) Month,WEEK(@ t) Week of the Year, DAY(@t) Day of the Month, HOUR(@t) Hour, MINUTE(@t) Minutes, SECOND(@t) Seconds
Run it, and it looks like this:
Second, the following describes several 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, that 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 a time unit such as day/month/year.
SUBDATE is the same, but it becomes a subtraction operation.
The full 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()：
Same as ADDDATE(expr, days) / SUBDATE(expr, days).
III. Interception of Date Interval
Next, using the usage of the date function described above, you can intercept the date interval.
1, 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
--PREVIOUS MONTH SELECT CURDATE() NOW, 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. The first four months
--FIRST FOUR MONTHS SELECT 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 Wednesday
Sometimes statistics are not carried out according to the natural week. You need to customize the starting and ending dates of the week. You can follow the following methods:
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 The above is how to extract the specified date interval in Mysql shared by everyone. If there is a similar doubt, please refer to the above analysis for understanding. If you want to know more about it, please pay attention to the industry information channel.
