Hi all

A table includes two DATE columns: FROM and TO.

I'm listing the contents by month. For a given month, all rows that exist 'across' that month are listed. For example, if I'm listing February, all the following rows are selected:

FROM 2010-01-10 TO 2010-05-10
FROM 2010-02-15 TO 2010-02-25
FROM 2010-02-15 TO 2010-03-05

I had some help creating the query to do this; it's here:

http://phpbuilder.com/board/showthread.php?p=10945655#post10945655

The next part I'm trying to work out is:

How many days there are in a given row within the result.

IE, using the example above:

FROM 2010-01-10 TO 2010-05-10 - days = 28
(because this item starts before 1st Feb and ends after end Feb, and there are 28 days in Feb 2010.

FROM 2010-02-15 TO 2010-02-25 - days = 11
(because this item is completely contained in this month so I can take the difference between the start and end date. The start and end dates are included too.

FROM 2010-02-15 TO 2010-03-05 - days = 14
(because this item starts on 15th Feb but ends after the end of Feb so only count to the end of the month that's being checked)

The date range I'm checking is in two variables:

$datefrom = '2010-02-01';
$dateto = '2010-02-28';

The table columns from the database are called from and to and are extracted as $from and $to.

I'm using a datediff function to calculate the difference between the days. I found this somewhere else and it's not doing quite what I want yet.

    function datediff($startdate, $enddate) {
    $start_ts = strtotime($startdate);
    $end_ts = strtotime($enddate);
    $diff = $end_ts - $start_ts;
    return round($diff / 86400);
    }

So far:

if ($from > $datefrom && $dateto > $to){   // whole month, so can use the range variables this time

$days = datediff ('$datefrom', '$dateto');
}
else {
// surely there's a simpler way of doing this!
}

I'm sure a more mathmatical approach to this would be better than a big list of if and else routines!

Any advice, welcome.

Thank you.

    Here's the logic as I've worked it out so far:

          if ($start <= $datefrom && $end >= $dateto)
      $days = $dateto-$datefrom;
      else if
         ($start >= $datefrom && $end <= $dateto)
      $days = $start-$end;
      else if
         ($start <= $datefrom && $end <= $dateto)
      $days = $start-$datefrom;
      else if
         ($start >= $datefrom && $end >= $dateto)
      $days = $dateto-$end;
      else
      $days = 'Unknown';

      Hmm,

      What about (in pseudo-code) ...

      datediff(min(end, to), max(start, from))

      P.

      PS. this is just a somewhat shorter version of yours

        Shorter is great! I got it working fine with the logic above (with some tweaking, but the principle is the same). It's fast and not too messy so perhaps sometimes there isn't going to be a nicer way of doing something like this.

        Still - anyone with comments is welcome to contribute.

        Thanks

          Hi again,

          Just thought I'd say - there is something to be said about longer code. If it's well laid out and easy to read then it's can also be easier to debug and easier to modify for other purposes.

          P.

            Write a Reply...