How to get data for 3 months, excluding weekend data of each month
Select temp, DATE_FORMAT(temp,'%b-%d') from test
where temp between
concat(year(now()),'-',month(now()) -3,'-', DATE_FORMAT(now(), '%d'))
and
(curdate());
I have been using this query to fetch data for 3 months, including 7 days a week, but I am not getting results as expected, how can I get data for 5 days a week on this query and ignore the output from the database ...
+3
source to share
1 answer
You can use a function DAYOFWEEK
that returns values from 1
(for Sunday) to 7
(for Saturday):
SELECT temp, DATE_FORMAT(temp,'%b-%d') FROM test
WHERE
DAYOFWEEK(temp) BETWEEN 2 AND 6
AND
temp BETWEEN
CONCAT(YEAR(NOW()),'-',MONTH(NOW())-3,'-',DATE_FORMAT(NOW(), '%d'))
AND
(CURDATE());
Note, however, that your conditions for selecting data from the last 3 months look a little suspicious. It's probably easier to do something like:
SELECT temp, DATE_FORMAT(temp,'%b-%d') FROM test
WHERE
DAYOFWEEK(temp) BETWEEN 2 AND 6
AND
temp >= NOW()-INTERVAL 3 MONTH;
+6
source to share