Days in a Range

PHP
1

Hi all,

I am trying to find the number of weekdays between two sets of dates, say, 2011-01-05 and 2011-04-05 (I would want both of these dates counted). However, I have a set of dates in the middle say 2011-02-23 and 2011-03-02 that I do not want these dates, nor the days in between them to count to my overall calculation.

Could somebody please point me in the right direction for what I am wanting to achieve? I am beginning to resign to the fact I may have to do each half separately rather than just subtract it at the end. Any ideas that could point me in the right direction would be great.

Adam

2

Convert the dates to timestamps, subtract start from end, and you’ve got the number of seconds between.
Do the same thing with your ‘voids’, subtract answer B from answer A, and you’ve got the number of valid seconds. Divide by 86400, and you’ve got the number of days counted.

3

starlion, what about saturdays and sundays? they were to be excluded

in fact, the “gotcha” in this type of question is usually to exclude holidays as well, and the best answer is to use a calendar table – one row per date, then counting the number of desired days would amount to something like …


SELECT COUNT(*)
  FROM calendar
 WHERE caldate BETWEEN '2011-01-05' AND '2011-04-05'
   AND caldate NOT BETWEEN '2011-02-23' AND '2011-03-02'
   AND weekday NOT IN ( 1,7 ) -- sun,sat
   AND holiday = 0
4

Completely missed the ‘weekdays’ part.
Doing it in pure PHP is a bit more tricky, but doesnt require a permanent table to be created.

Assuming non-stupid input (Start/end date inside of a break, etc)


$start = ####-##-##
$end = ####-##-##
$skip = AAAA-AA-AA,BBBB-BB-BB,AAAA-AA-AA,BBBB-BB-BB (etc. A = start of break, B = end of break)

//End Input
$curtime = strtotime($start);
$endtime = strtotime($end);
$breaks = explode(',',$skip);
$curbreak = 0;
$countflag = 1;

$days = 1; //Added 1 for end.
while($curtime <= $endtime) {
  if($curtime == strtotime($breaks[$curbreak])) {
   $countflag = ($countflag + 1) % 2; //Toggle.
   $curbreak++;
  }
  if($countflag && date('N',$curtime) != 6 && date('N',$curtiem) != 7) {
    $days++;
  }
  $curtime += 86400;
}
echo $days;

(Doing this off the top of my head, haven’t tried it.)

5

I’ve already noticed a flaw in my own code - the BBBB-BB-BB should be the date you RETURN, and -DOES- count. So if you were on break from 2011-03-23 to 2011-03-28, you would need to put 2011-03-29 in as the “B” date for that break period.

EDIT: And $days should be initialized to 0 not 1. Woot. Debug phase.

6

w00t – sql simply rulez!!

7

Yeah… r937, would you like to give the SQL code for generating this table of awesomeness that you would need?

8

The debugged code:


<?php
//Sample Input
$start = "2011-04-05";
$end = "2011-04-15";
$skip = "2011-04-13,2011-04-14"; //I took 4/13 off. So I left on the 13, and returned on the 14th.
//End Input

$curtime = strtotime($start);
$endtime = strtotime($end);
$breaks = explode(',',$skip);
$curbreak = 0;
$countflag = 1;

$days = 0;
while($curtime <= $endtime) {
  if($curtime == strtotime($breaks[$curbreak])) {
   $countflag = ($countflag + 1) % 2; //Toggle.
   if($curbreak != count($curbreak) {
     $curbreak++;
   }
  }
  if($countflag && date('N',$curtime) != 6 && date('N',$curtime) != 7) {
    $days++;
  }
  $curtime += 86400;
}
echo $days; // 8
?>
9

sure…

CREATE TABLE calendar
( caldate  DATE NOT NULL PRIMARY KEY
, weekday  TINYINT NOT NULL
, stathol  TINYINT NOT NULL
);
INSERT INTO calendar VALUES
 ( '2011-04-01' , 6 , 0 )
,( '2011-04-02' , 7 , 0 )
,( '2011-04-03' , 1 , 0 )
,( '2011-04-04' , 2 , 0 )
,( '2011-04-05' , 3 , 0 )
,( '2011-04-06' , 4 , 0 )
,( '2011-04-07' , 5 , 0 )
,( '2011-04-08' , 6 , 0 )
,( '2011-04-09' , 7 , 0 )
,( '2011-04-10' , 1 , 0 )
,( '2011-04-11' , 2 , 0 )
,( '2011-04-12' , 3 , 0 )
,( '2011-04-13' , 4 , 0 )
,( '2011-04-14' , 5 , 0 )
,( '2011-04-15' , 6 , 0 )
,( '2011-04-16' , 7 , 0 )
,( '2011-04-17' , 1 , 0 )
,( '2011-04-18' , 2 , 0 )
,( '2011-04-19' , 3 , 0 )
,( '2011-04-20' , 4 , 0 )
,( '2011-04-21' , 5 , 0 )
,( '2011-04-22' , 6 , 1 ) -- good friday
,( '2011-04-23' , 7 , 0 )
,( '2011-04-24' , 1 , 0 )
,( '2011-04-25' , 2 , 0 )
,( '2011-04-26' , 3 , 0 )
,( '2011-04-27' , 4 , 0 )
,( '2011-04-28' , 5 , 0 )
,( '2011-04-29' , 6 , 0 )
,( '2011-04-30' , 7 , 0 )
;

let’s have a race, shall we? you modify your fully debugged code, and i’ll modify mine…

how many fridays that aren’t a stat holiday between april 5th and april 28th?

ready… set… go…

SELECT COUNT(*)
  FROM calendar
 WHERE caldate BETWEEN '2011-04-05' AND '2011-04-28'
   AND weekday = 6 -- fri
   AND stathol = 0

answer: 2

elapsed time to develop solution: approx 12 seconds

10

So now just do that for every day in history, and you’ll be good to go :smiley:

(And yes, before someone points it out, i know that using strtotime means my options are limited by the Epoch… but at least i dont have to store every day from 1970 to 2038 in a table to use it :wink: )

11

you could actually write a small piece of code to populate it easily, rather than typing in the dates like i did

and you would only have to store the dates that will be queried – in my experience, this is often no more than a couple years back and a couple years forward, and in actual practice, many organizations only need it for the current year, so it’s something you can do in the doldrums between christmas and new year’s if you are actually stuck in the office

:wink: