4

I have 2 dates. Lets say they look like this.

$start = 2010/12/24;
$end = 2012/01/05;

I query the database to look for visits between these two dates. I find some. I then populate an array called stats.

$stats['2010/12/25'] = 50;
$stats['2010/12/31'] = 25;
...

As you can see, there are days missing. I need to fill the missing dates with a value of zero. I was thinking something like this. (I have pulled day / month / year from start and end dates.

for($y=$start_year; $y <= $end_year; $y++) {
    for($m=$start_month; $m <=$end_month; $m++) {
        for($d=$start_day; $d <= $end_day; $d++) {

This would work fine for the year however the months and days wouldn't work. If the start day is the 15th. Days 1-14 of each subsequent month would be missed. I could have a solution like this then...

for($y=$start_year; $y <= $end_year; $y++) {
    for($m=1; $m <13; $m++) {
         $total_days = cal_days_in_month(CAL_GREGORIAN, $m, $y) + 1;
         for($d=1; $d <= $total_days; $d++) {

I would then need a bunch of if statements making sure starting and end months and days are valid.

Is there a better way of doing this? Or could this even be done in my mysql query?

ComputerUser
  • 4,828
  • 15
  • 58
  • 71
  • possible duplicate of [PHP: Loop thru all months in date range?](http://stackoverflow.com/questions/2155110/php-loop-thru-all-months-in-date-range) – Gordon Jan 06 '12 at 11:03
  • possible duplicate of http://stackoverflow.com/questions/2736784/how-to-find-the-dates-between-two-specified-date – Gordon Jan 06 '12 at 11:04
  • please do not ask date time related questions. they have all been answered already. – Gordon Jan 06 '12 at 11:04

6 Answers6

16

Just to demonstrate the power of some of PHP's newer interval handling method (mentioned by pgl in his answer):

$startDate = DateTime::createFromFormat("Y/m/d","2010/12/24",new DateTimeZone("Europe/London"));
$endDate = DateTime::createFromFormat("Y/m/d","2012/01/05",new DateTimeZone("Europe/London"));

$periodInterval = new DateInterval( "P1D" ); // 1-day, though can be more sophisticated rule
$period = new DatePeriod( $startDate, $periodInterval, $endDate );

foreach($period as $date){
   echo $date->format("Y-m-d") , PHP_EOL;
}

Does require PHP >= 5.3.0

EDIT

If you need to include the actual end date, then you need to add a day to $endDate immediately before the foreach() loop:

$endDate->add( $periodInterval );

EDIT #2

$startDate = new DateTime("2010/12/24",new DateTimeZone("Europe/London"));
$endDate = new DateTime("2012/01/05",new DateTimeZone("Europe/London"));

do {
   echo $startDate->format("Y-m-d") , PHP_EOL;
   $startDate->modify("+1 day");
} while ($startDate <= $endDate);

For PHP 5.2.0 (or earlier if dateTime objects are enabled)

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • Just curious. Why did you go with the parameter syntax instead of string concatenation in your `echo` statement? I always use `.` so I can easily change the line of code to be assigned to a variable instead of being `echo`ed out. – Treffynnon Jan 06 '12 at 09:39
  • @Treffynnon - Just force of habit I guess.... some people say it's quicker than concatenation, but I've never tested that theory... but I rarely use echo in working code, normally only for testing simple routines. – Mark Baker Jan 06 '12 at 09:41
  • Thanks, unfortunately the server I am using is PHP 5.2 :( – ComputerUser Jan 06 '12 at 09:56
  • @JasonS - I assume PHP has dateTime objects enabled, so using dateTime objects and the modify() method - http://uk.php.net/manual/en/datetime.modify.php - should work OK, and prevent any issues with daylight savings – Mark Baker Jan 06 '12 at 10:09
4

If you're using PHP5.3 then Mark Baker's answer is the one to use. If (as you say in your comment) you're still on PHP5.2 something like this should help you:

$startdate = strtotime( '2010/12/24' );
$enddate = strtotime( '2012/01/05' );
$loopdate = $startdate;
$datesArray = array();
while( $loopdate <= $enddate ) {
   $datesArray[$loopdate] = 0;
   $loopdate = strtotime( '+1 day', $loopdate );
}

It will create an array of the unix timestamp of every date between the start and end dates as the index and each value set to zero. You can then overwrite any actual results you have with the correct values.

liquorvicar
  • 6,081
  • 1
  • 16
  • 21
2
$start_date = DateTime::createFromFormat('Y/m/d', '2010/12/24');
$end_date = DateTime::createFromFormat('Y/m/d', '2012/01/05');

$current_date = $start_date;

while($current_date <= $end_date) {
    $current_date = $current_date->add(new DateInterval('P1D'));
    // do your array work here.
}

See DateTime::add() for more information about this.

Treffynnon
  • 21,365
  • 6
  • 65
  • 98
1
$i = 1;
while(date("Y/m/d", strtotime(date("Y/m/d", strtotime($start)) . "+ $i days")) < $end) {
    ... code here ...
    $i++;
}
MattP
  • 2,798
  • 2
  • 31
  • 42
1

I would calculate the difference between start and end date in days, iterate on that adding a day to the timestamp on each iteration.

$start = strtotime("2010/12/24");
$end = strtotime("2012/01/05");

// start and end are seconds, so I convert it to days 
$diff = ($end - $start) / 86400; 

for ($i = 1; $i < $diff; $i++) {
    // just multiply 86400 and add it to $start
    // using strtotime('+1 day' ...) looks nice but is expensive.
    // you could also have a cumulative value, but this was quicker
    // to type
    $date = $start + ($i * 86400); 

    echo date('r', $date);
}
nikc.org
  • 16,462
  • 6
  • 50
  • 83
  • Old school method, Mark Bakers answer is the preferred approach in my opinion. – Tim at MastersAllen Feb 18 '19 at 15:41
  • @TimatMastersAllen indeed. His is also the selected answer. Please, also note that you're commenting on a 7 year old answer. – nikc.org Feb 19 '19 at 17:08
  • 1
    @nikc,org Yes I was aware of the dates, but you'll note both answers were at a similar time. Not to worry, the comment was really for developers considering using this method as it is commented on as "Perfec answer" and someone may still use this today. – Tim at MastersAllen Feb 21 '19 at 09:34
  • Fair point, but it was more bleeding edge back in the days. FWIW I also favoured using the DateTime class (and friends), but many PHP hosting providers had ancient PHP versions installed, causing very slow adoption of more modern APIs. – nikc.org Feb 22 '19 at 10:09
0

I have this bit of horrible code saved:

while (($tmptime = strtotime('+' . (int) $d++ . ' days', strtotime($from))) && ($tmptime <= strtotime($to)))    // this code makes baby jesus cry
    $dates[strftime('%Y-%m-%d', $tmptime)] = 0;

(Set $from and $to to appropriate values.) It may well make you cry, too - but it sort of works.

The proper way to do it is to use DateInterval, of course.

pgl
  • 7,551
  • 2
  • 23
  • 31