John
John

Reputation: 487

I need to check multiple date ranges to see if they have a same day more than 3 times

Okay, so I have a start datetime and an end datetime for multiple items. I need to find out if there are more than 3 items that are within the same datetime as each other. I just can't wrap my head around this one, I can get the days of each individual item into an array using this function

function getDatesBetween2Dates($startTime, $endTime) {
$day = 86400;
$format = 'm/d/y g:i A';
$startTime = strtotime($startTime);
$endTime = strtotime($endTime);
$numDays = round(($endTime - $startTime) / $day) + 1;
$days = array();
for ($i = 0; $i < $numDays; $i++) {
    $days[] = date($format, ($startTime + ($i * $day)));
}
return $days;
}

The problem is, it only get the first time and just adds a full day to every day afterward. it doesn't take into account for the last days time. I just can't think of an efficient way to do this.

Just to clarify, let me give you an example. I have 5 items in my table, they each have a datetime range.. start and end. I need to check if 4 or more of those items have any days in their date range that are the same. Can anyone point me in the right direction? Thanks.

Upvotes: 1

Views: 491

Answers (3)

Borodin
Borodin

Reputation: 126722

Something like this perhaps?

  • Create a list of dates extending from the earliest start date to the latest end date in your items

  • Iterate over the items adding each item id to every date within its range

  • Each date in the list now has a set of ids for items whose range incude that date
  • Count the number of items against each date. If any have four or more ids then your condition is true

`

class Item {
  private static $next_id = 0;
  public $id;
  public $start, $end;

  public function __construct($start, $end) {
    $this->id = self::$next_id++;
    $this->start = $start;
    $this->end = $end;
  }
}

$items = array(
  new Item('06-Dec-2011', '12-Dec-2011'),
  new Item('01-Dec-2011', '04-Dec-2011'),
  new Item('02-Dec-2011', '07-Dec-2011'),
  new Item('07-Dec-2011', '09-Dec-2011'),
  new Item('06-Dec-2011', '10-Dec-2011'),
);

foreach ($items as $item) {
  $start = strtotime($item->start);
  $end = strtotime($item->end);
  for ($day = $start; $day <= $end; $day += 24 * 60 * 60) {
    $dates[$day][] = $item->id;
  }
}

foreach ($dates as $day => $ids) {
  $count = sizeof($ids);
  if ($count > 3) {
    echo $count, " items found on ", date('d-M-Y', $day), "\n";
    foreach ($ids as $id) {
      echo "  Item ", $id, "\n";
    }
  }
}

Upvotes: 1

Jonathan Rich
Jonathan Rich

Reputation: 1738

Change for ($i = 0; $i < $numDays; $i++) { to for ($i = 0; $i <= $numDays; $i++) { to get all dates in the range, including the last date.

Unfortunately you're going about this the wrong way. Here's some pseudocode of how I would do it:

Iterate through the list of date ranges
    Log the earliest start date
    Log the last end date

Iterate through all dates between the earliest start date and the last end date
    Iterate through the list of date ranges, to determine how many date ranges contain the current date
        If more than three date ranges contain the current date, then store it in an array of conflicting dates

I don't believe this is the most efficient way, but unless you're dealing with huge date ranges or quite a large number of dates, it will be relatively fast.

Upvotes: 1

symcbean
symcbean

Reputation: 48357

"in my table" - then save yourself a lot of pain and ask the DB to find them for you. It'll be a lot simpler and a lot faster.

I've no idea what "are within the same datetime" means.

Do you mean the same calendar day? The same week day? The same 24 hour period? The same time to an accuracy of the resolution of the data (usually seconds)? Something else? Your code suggests you are actually looking for overlapping ranges, and not explicit events.

So something like:

SELECT a.id, b.id, c.id
FROM atable a,
atable b,
atable c
WHERE a.start_time<=b.start_time
AND b.start_time<=c.start_time
AND a.id<>b.id
AND b.id<>c.id
AND a.id<>c.id
AND a.start_time<=b.end_time
AND a.end_time>=b.start_time
AND b.start_time>=c.end_time
AND b.end_time>=c.start_time;

You just need to handle the cartesian product where there are 4 or more items overlapping.

Upvotes: 1

Related Questions