Number of e.g. Mondays left in month
How do you most easily calculate how many e.g. Mondays are left in a month using MySQL (counting today)?
Bonus points for a solution that solves it f开发者_运维问答or all days of the week in one query.
Desired output (run on Tuesday August 17th 2010):
dayOfWeek left
1 2 -- Sunday
2 2 -- Monday
3 3 -- Tuesday (yep, including today)
4 2 -- Wednesday
5 2 -- Thursday
6 2 -- Friday
7 2 -- Saturday
Create a date table that contains one row for each day that you care about (say Jan 1 2000 - Dec 31 2099):
create table dates (the_date date primary key);
delimiter $$
create procedure populate_dates (p_start_date date, p_end_date date)
begin
declare v_date date;
set v_date = p_start_date;
while v_date <= p_end_date
do
insert ignore into dates (the_date) values (v_date);
set v_Date = date_add(v_date, interval 1 day);
end while;
end $$
delimiter ;
call populate_dates('2000-01-01','2099-12-31');
Then you can run a query like this to get your desired output:
set @date = curdate();
select dayofweek(the_date) as dayOfWeek, count(*) as numLeft
from dates
where the_date >= @date
and the_date < str_to_date(period_add(date_format(@date,'%Y%m'),1),'%Y%m')
group by dayofweek(the_date);
That will exclude days of the week that have 0 occurrences left in the month. If you want to see those you can create another table with the days of the week (1-7):
create table days_of_week (
id tinyint unsigned not null primary key,
name char(10) not null
);
insert into days_of_week (id,name) values (1,'Sunday'),(2,'Monday'),
(3,'Tuesday'),(4,'Wednesday'),(5,'Thursday'),(6,'Friday'),(7,'Saturday');
And query that table with a left join to the dates table:
select w.id, count(d.the_Date) as numLeft
from days_of_week w
left outer join dates d on w.id = dayofweek(d.the_date)
and d.the_date >= @date
and d.the_date < str_to_date(period_add(date_format(@date,'%Y%m'),1),'%Y%m')
group by w.id;
i found something
according to this article "find next monday"
http://www.gizmola.com/blog/archives/99-Finding-Next-Monday-using-MySQL-Dates.html
SELECT DATE_ADD(CURDATE(), INTERVAL (9 - IF(DAYOFWEEK(CURDATE())=1, 8,
DAYOFWEEK(CURDATE()))) DAY) AS NEXTMONDAY;
what we need to do is calculate the days between end month and next Monday, and divide in 7 .
update (include current day) :
so the result is like :
for Monday
SELECT CEIL( ((DATEDIFF(LAST_DAY(NOW()),DATE_ADD(CURDATE(),
INTERVAL (9 - IF(DAYOFWEEK(CURDATE())=1, 8, DAYOFWEEK(CURDATE()))) DAY)))+1)/7)
+ IF(DAYOFWEEK(CURDATE())=2,1,0)
for Tuesday :
SELECT CEIL( ((DATEDIFF(LAST_DAY(NOW()),DATE_ADD(CURDATE(),
INTERVAL (10 - IF(DAYOFWEEK(CURDATE())=1, 8, DAYOFWEEK(CURDATE()))) DAY)))+1)/7)
+ IF(DAYOFWEEK(CURDATE())=3,1,0)
Have a look at my responses to;
MySQL: Using the dates in a between condition for the results
and
Select all months within given date span, including the ones with 0 values
for a way I think would work nicely, similar to @Walker's above, but without having to do the dayofweek() function within the query, and possibly more flexible too. One of the responses has a link to a SQL dump of my table which can be imported if it helps!
精彩评论