0

I need to calculate number of spesific days (ex. wednesday & thursday) between two dates.

I know about time function and all related ones, but I do not know how to use them in this context.

The problem solved in two solutions, but it seems I can't choose booth solution. Thank you Andrius Naruševičius and Kickstart for your enlightment :D

Smiley
  • 98
  • 1
  • 2
  • 9
  • this works for you? http://stackoverflow.com/questions/2546053/mysql-difference-between-two-timestamps-in-days – alpera Aug 27 '13 at 08:07
  • Google first -.- First result does what you want - http://stackoverflow.com/questions/9757919/count-days-between-two-dates-excluding-weekends-mysql-only – Andrius Naruševičius Aug 27 '13 at 08:08
  • @alpera IMHO, datediff count how many days between date. I need only spesific days – Smiley Aug 27 '13 at 08:12
  • @AndriusNaruševičius I had read that, but it confuse me for selecting the specific days to count, need some enlightenment here :) – Smiley Aug 27 '13 at 08:14
  • define spesific days? you mean the difference between 08/27 - 08/20 or what? edit: well ok, sorry just got what you mean.. – alpera Aug 27 '13 at 08:18
  • this works? http://stackoverflow.com/questions/9491989/calculating-a-daily-average-or-how-many-mondays-are-in-a-date-range – alpera Aug 27 '13 at 08:23
  • @alpera what I need just a single query, the closest one to my problems is http://stackoverflow.com/questions/9757919/count-days-between-two-dates-excluding-weekends-mysql-only, but sadly the logic confuse me, still wondering on how if I want it count for different day, example 3 different day in a week (sunday, wednesday and friday) – Smiley Aug 27 '13 at 08:29

3 Answers3

1

Method taken and adapted from here

@S          = start date
@E          = end date, not inclusive
@full_weeks = floor( ( @E-@S ) / 7)
@days       = (@E-@S) - @full_weeks*7   OR (@E-@S) % 7

SELECT
  @full_weeks*1 -- saturday
 +IF( @days >= 1 AND weekday( S+0 )=5, 1, 0 )
 +IF( @days >= 2 AND weekday( S+1 )=5, 1, 0 )
 +IF( @days >= 3 AND weekday( S+2 )=5, 1, 0 )
 +IF( @days >= 4 AND weekday( S+3 )=5, 1, 0 )
 +IF( @days >= 5 AND weekday( S+4 )=5, 1, 0 )
 +IF( @days >= 6 AND weekday( S+5 )=5, 1, 0 )

Done.

Working SQL Fiddle

Community
  • 1
  • 1
Andrius Naruševičius
  • 8,348
  • 7
  • 49
  • 78
  • 1. @full_weeks*1 -- saturday (what does 1 mean in here??) – Smiley Aug 27 '13 at 08:47
  • 2. why it have to ... =5 ???? – Smiley Aug 27 '13 at 08:47
  • I try with start date '2013-08-01' and end date '2013-08-27', the result is 3. If it count saturday it should be 4, right? – Smiley Aug 27 '13 at 08:51
  • 1. for every full week you get one saturday 2. for every saturday (saturday = 5) outside the full weeks you add get another saturday. 3. Yes I count 4 saturdays too, check whether `@full_weeks`, `days` get returned correct. 4. Make sql fiddle and I will take a look. – Andrius Naruševičius Aug 27 '13 at 11:45
  • hi thanks for your reply, I had try it and it workin good, but then I try to find another day, wich is Friday=3 and increase the end date range into "2013-08-29" the resul still count 4, when it should be 5. Here's the Fiddle http://sqlfiddle.com/#!2/d41d8/19106 – Smiley Aug 28 '13 at 03:12
  • First of all, Friday is 4. Secondly, the interval's end is NOT inclusive (this IS mentioned in the code I copied), so if @E is `2013-08-29`, then it only counts up to and including `2013-08-28`. I checked your example with the correct values and it showed me 5 :) – Andrius Naruševičius Aug 28 '13 at 05:58
  • My mistake :D, sory about that, the code seems run perfectly well, thanks for the enlightment :) – Smiley Aug 28 '13 at 07:12
1

An alternative way of doing it with a SELECT statement:-

SELECT DAYNAME(DATE_ADD(@StartDate, INTERVAL (Units.i + Tens.i * 10 + Hundreds.i * 100) DAY)) AS aDayOfWeek, COUNT(*)
FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS Units
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS Tens
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS Hundreds
WHERE DATE_ADD(@StartDate, INTERVAL (Units.i + Tens.i * 10 + Hundreds.i * 100) DAY) <= @EndDate
GROUP BY aDayOfWeek

This will work with dates up to 1000 days apart but easy to expand it for larger date ranges.

What it is doing is generating a range of numbers (starting at 0) and adding them to the start date where the result is <= to the end date. Then getting the day names of each of those and getting a count of each one.

Kickstart
  • 21,403
  • 2
  • 21
  • 33
0

I think it's easier to use a calendar table. To find out how many Wednesdays and Thursdays there are in March 2013, for example, you'd do something like this.

select count(*)
from calendar
where cal_date between '2013-03-01' and '2013-03-31'
  and day_of_week in ('Wed', 'Thu')

count
--
8

To extend it to count Sundays, too, just add 'Sun' to the IN clause. (Although I have to say this strikes me as kind of a weird thing to do.)

select count(*)
from calendar
where cal_date between '2013-03-01' and '2013-03-31'
  and day_of_week in ('Wed', 'Thu', 'Sun')

count
--
13

Here's code for a calendar table in PostgreSQL. You'll have to jump through some hoops to translate it for MySQL, because MySQL doesn't enforce CHECK constraints.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • unfortunately I dont want to use any table for it, just a single query. But it can done when you combine with this solution http://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query?lq=1 .... thanks for the idea :) – Smiley Aug 28 '13 at 03:34