PHP Array with date/time and duration for highchart

Question

So first here's Fiddle of what I am trying to achieve in the end: Fiddle

To do so I need to get values from three different tables: ok_part, reject and stoppage.

As for stoppage, the table structure looks like this:

The x Axis of my chart are one hour gaps, the first x Axis is always time 8 hours ago and the last one time from now to next full hour.

I've managed to put together ok_part and reject arrays for highchart.

class GraphCalc() {
  .....
  /* calculate time */
  function CalculateTime($diff, $form) {

    $new = new DateTime();
    $new->modify($diff);
    return $form != 0 ? $new->format('Y-m-d H:00') : $new->format('Y-m-d');
  }

  function GraphInterval() {

    $time = strtotime($this->CalculateTime('-8 hours', 1)); // calling function to get time 8 hours ago
    $now = strtotime(date('Y-m-d H:00'));

    /* looping through time (8 hours ago to now) */
    for ($i = $time, $j = $i + 3600; $i <= $now; $i += 3600, $j += 3600) {

        /* puts together array for x Axis */
        $this->xAxis_array[] =  "'".date('H:00', $i).' - '.date('H:00', $j)."'";

        /* queries to sum ok parts and rejects according to time */
        $ok_part_sum[] = MouldingPart::find(
            array(
                'select' => 'SUM(amount) as ok_qty', 
                'conditions' => array(
                    'moulding_product_id IN (?) AND date >= ? AND date <= ?', 
                    $this->product_id, 
                    date('Y-m-d H:00', $i), 
                    date('Y-m-d H:00', $j)
                )
            )
        )->ok_qty;
        $reject_part_sum[] = MouldingReject::find(
            array(
                'select' => 'SUM(amount) as reject_qty', 
                'conditions' => array(
                    'moulding_product_id IN (?) AND date >= ? AND date <= ?', 
                    $this->product_id, 
                    date('Y-m-d H:00', $i), 
                    date('Y-m-d H:00', $j)
                )
            )
        )->reject_qty;
    }

    /* looping through ok_part and reject arrays to add 0 where there's no value */
    for ($i = 0; $i < count($ok_part_sum); $i++) { 
        $this->chart_ok_array[] = $ok_part_sum[$i] == '' ? 0 : $ok_part_sum[$i];
        $this->chart_reject_array[] = $reject_part_sum[$i] == '' ? 0 : $reject_part_sum[$i];
    }
  }

  function xAxis() {
    /* return x Axis values */
    return implode(', ',$this->xAxis_array);
  }

  function yAxisValues() {
    /* put together arrays for highchart */
    $chart_data[] = "{data: [".implode(", ",$this->chart_ok_array)."], name: 'OK', color: '#89A54E'}";
    $chart_data[] = "{data: [".implode(", ",$this->chart_reject_array)."], name: 'PRAAGID', color: '#AA4643'}";


    return $chart_data;
  }
}

I've would like help to get a solution for stoppage data. Stoppage has two dates that need to be considered: start_date and end_date. Based on the x Axis and stoppage dates I need to calculate the duration. Sometimes I could probably use the duration field, but I think it is easier to avoid it and just calculate it on the fly.

Here's an example of what I would like to achieve with stoppage:

Let's say x Axis is 05:00 - 06:00 then stoppage duration should be 59 minutes, because there is an entry in stoppage table with start_date 2013-09-05 05:01:00 and end_date 2013-09-05 09:00:00, but I can't use the end date of stoppage table value, because I need to use the x axis value which is 06:00.

I hope you get an idea of what I am trying to achieve, it is really hard to explain it.

The data in stoppage table might vary as well. In one hour there might be multiple stoppages. Let's say x Axis is 08:00 - 09:00 and in table there are three stoppages:

start_date: 07:54 end_date: 08:15
start_date: 08:19 end_date: 08:38
start_date: 08:45 end_date: 09:47
So the duration from 08:00 - 09:00 should be 49 minutes.

Please help me with this, I've been stuck with this for days now.. I tried to explain it as good as I could, but I bet it's still confusing.

In the end I would like to get an array for highchart, like I have with ok_part and reject.

Answer 1

The idea is to use TIMESTAMPDIFF that will substract the end_date with the start_date :

$stoppage_part_sum[] = MouldingStoppage::find(
    array(
        'select' => 'SUM(
            ABS(TIMESTAMPDIFF(
                MINUTE,
                IF(end_date > \''.date('Y-m-d H:00', $j).'\', \''.date('Y-m-d H:00', $j).'\', end_date),
                IF(start_date < \''.date('Y-m-d H:00', $i).'\', \''.date('Y-m-d H:00', $i).'\', start_date)
            ))
        ) as stoppage_qty', 
        'conditions' => array(
            'moulding_product_id IN (?) AND end_date >= ? AND start_date <= ?', 
            $this->product_id, 
            date('Y-m-d H:00', $i), 
            date('Y-m-d H:00', $j)
        )
    )
)->stoppage_qty;

The IF(end_date > ...) and IF(start_date < ...)is to take only the time into the current window. It will then use this :

start_date: 08:00 end_date: 08:15
start_date: 08:19 end_date: 08:38
start_date: 08:45 end_date: 09:00

Then you just TIMESTAMPDIFF end_date and start_date and SUM them.

Note the condition to match the concerned "stoppages". (start_date < j and end_date > i)

The demo sqlFiddle

Try to change the dates in data to watch the result.