Non-overlapping minutes per day

Question

I have been cracking my head trying to resolve this problem.

I need to know how many minutes of the day are being worked by a staff member alone in the shop.

Here is the data for daynumber = 0 (monday):

For this day, the staff member with staffid = 32 is alone from 11:00 to 11:05 in the shop.

What I have so far, is just adding all starting times, but basically what I'm thinking is, if I have any way of knowing a staff member is alone, I can calculate time between the index and the next.

for($i=0; $i<count($results); $i++){
    if(isset($results[$i+1])){
        if($results[$i]->starttime < $results[$i+1]->starttime)
            $start = strtotime($results[$i]->starttime);
            $end = strtotime($results[$i+1]->endtime);
            $minutes +=  idate('i', $end - $start);
        }
    }
}

Any thoughts?

UPDATE 1: I get to this but still no luck;

    for($i=0; $i<count($results); $i++){
        if(isset($results[$i+1])){
            $StartDate1 = strtotime($results[$i]->starttime);
            $EndDate1 = strtotime($results[$i]->endtime);
            $StartDate2 = strtotime($results[$i+1]->starttime);
            $EndDate2 = strtotime($results[$i+1]->endtime);

            if(($StartDate1 <=  $EndDate2) && ($EndDate1 >= $StartDate2)){
                $StartDate1 = idate('i', $StartDate1);
                $EndDate1 = idate('i', $EndDate1);
                $StartDate2 = idate('i', $StartDate2);
                $EndDate2 = idate('i', $EndDate2);

                $a = abs($EndDate1 - $StartDate1);
                $b= abs($EndDate1 - $StartDate2);
                $c = abs($EndDate2 - $StartDate2);
                $d = abs($EndDate2 - $StartDate1);

                $minutes += min([$a,$b,$c,$d]);
            }
        }
    }

What am I doing wrong?

We (maybe) try help fix code you've written. We're not here to write code for you, or design your system. That's your job. — Marc B, Jul 15 '16 at 18:41
Something here could be of help: [Mysql -> Date and Time Functions](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html) — FirstOne, Jul 15 '16 at 18:45
You can refer the old stack overflow post for the answer: http://stackoverflow.com/questions/365191/how-to-get-time-difference-in-minutes-in-php — Gautam Krishna R, Jul 15 '16 at 18:58
I think it's a little unfair to close this question. It is at least vaguely difficult, and quite interesting. — Strawberry, Jul 15 '16 at 20:07
Thank you @Progrock, it looks like this may be the way to solve this. — Limker, Jul 16 '16 at 15:46

score 0 · Answer 1 · answered Jul 15 '16 at 20:14

Here's one idea, using a utility table - in this case a table of integers from 0-9. Utility tables are frowned on by some, but I like them because they mean less typing. You can always replace the table with a string of UNIONs.

This is for all days. I might modify it later to show how you could filter for a specific day.

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT SEC_TO_TIME((i4.i*1000+i3.i*100+i2.i*10+i1.i)*60) n 
  FROM ints i1 
  JOIN ints i2 
  JOIN ints i3 
  JOIN ints i4 
  JOIN 
     ( SELECT daynumber
            , MIN(starttime) starttime
            , MAX(CASE WHEN endtime < starttime THEN SEC_TO_TIME(TIME_TO_SEC('24:00:00')+TIME_TO_SEC(endtime)) ELSE endtime END) endtime 
         FROM my_table 
        GROUP 
           BY daynumber
     ) x 
    ON SEC_TO_TIME((i4.i*1000+i3.i*100+i2.i*10+i1.i)*60) BETWEEN x.starttime AND x.endtime
  JOIN my_table y
    ON SEC_TO_TIME((i4.i*1000+i3.i*100+i2.i*10+i1.i)*60) BETWEEN y.starttime AND CASE WHEN y.endtime < y.starttime THEN SEC_TO_TIME(TIME_TO_SEC('24:00:00')+TIME_TO_SEC(y.endtime)) ELSE y.endtime END
 GROUP
    BY n HAVING COUNT(*) = 1;

The number of lone minutes is equal to the number of rows in this result.

Thank you for your response @Strawberry, but I'm looking for a PHP solution. I'm still on this, if I found to figure this out, I'll post it. Thanks! — Limker, Jul 16 '16 at 15:44

Non-overlapping minutes per day

1 Answers1