Determining time ranges within pre-defined shifts in MySQL

Question

I'm working with a timecard database and trying to determine how much time for each punch falls into each one of three distinct shift periods.

For example
shift 1 = 7AM - 3pm
shift 2 = 3pm - 11pm
shift 3 = 11pm - 7am

Joe clocks in at 6:45AM and out at 1:45PM

15 minutes of this would need to be calculated as time on shift 3, but I'm not sure how to go about slicing out that bit of time in MySQL. All I have are a time in and time out field.

There are three shift periods:

Shift  TimeStart   TimeEnd
1      07:00       15:00
2      15:00       23:00
3      23:00       07:00

Sample Data

ID    TimeIn            TimeOut           Hours
100   2014-07-31 06:45  2014-07-31 13:45  7

Desired Result

ID    Shift  TimeWorked
100   1      06:45
100   2      00:00
100   3      00:15

SQL Fiddle

Answer 1

First you need to add day column for differentiate time from 23:59:59 to next day time.

id| shift_name | time_start | time_end | day 1 | Day Shift | '07:00:00' | '18:59:59' | 1 2 | Night Shift | '19:00:00' | '06:59:59' | 2

Procedure :

DELIMITER $$ CREATE PROCEDURE sp_check_shift(IN intime time) PROC: begin IF(intime>='00:00:01' AND intime<='23:59:59') THEN IF ( SELECT 1 FROM tbl_shift WHERE time_start<=intime AND time_end>=intime AND day=1) THEN SELECT shift_name FROM tbl_shift WHERE time_start<=intime AND time_end>=intime AND day=1;ELSEIF ( SELECT 1 FROM tbl_shift WHERE time_start<=intime AND day=2) THEN SELECT shift_name FROM tbl_shift WHERE time_start<=intime AND day=2;ELSEIF ( SELECT 1 FROM tbl_shift WHERE time_end>=intime AND day=2) THEN SELECT shift_name FROM tbl_shift WHERE time_end>=intime AND day=2;END IF;ELSE SELECT 'Invalid Time' shift_name;END IF;END$$ delimiter ;

Answer 2

I'd do this in PHP. Looking at Joe's example, it is initially tempting to try to work out how his data maps onto the shift rules. However, I think it would be a neater solution to do it the other way around i.e. map the rules onto his data, until there is no data to classify.

The algorithm might go a bit like this:

• Joe's remaining time is 6:45 - 13:45

Let's map the first rule onto it (i.e. how much of this rule contributes to that range?):

• shift 1 = 7:00 - 15:00 (6:45 hours)

Now Joe's remaining time is:

• 6:45 - 7:00

Do the next rule:

• shift 2 = 15:00 - 23:00 (0 hours)

Joe's remaining time is therefore unchanged. And finally the last rule:

• shift 3 = 23:00 - 1d7:00 (0:15 hours)

There are a few things to note:

• The amount of worked time could be stored in an array (a "worked time set"). It starts off as a simple start and end, but if a rule removes a chunk of time from the middle, it may split into two starts and two ends
• When applying a rule, convert them to actual timestamps (i.e. a date and a time) so the wrapping to the next day works correctly
• Write a function that takes a worked time set, plus a rule start and end timestamp, modifies a worked time set, and returns a number of hours for the rule

Answer 3

I was able to come up with a solution for this using PHP.

What I did was loop through each punch, minute by minute, and determine what shift each one minute time span applies to. Within the loop, I increment one of 4 variable for shifts 1, 2, 3 or 0(no shift pay), and at the end, dump those variables to the database for the record being analyzed.

$query = "SELECT * FROM source_filtered_timecard";
$result_set = mysqli_query($connection, $query);

while($record = mysqli_fetch_assoc($result_set)) {
    $checkCount++;
    $shift1_hours = 0; $shift2_hours = 0;
    $shift3_hours = 0; $shift0_hours = 0;

    $time = strtotime($record['in_time']); 
    $time_out = strtotime('-1 Minute',strtotime($record['out_time']));

    while($time <= $time_out) {
        $mysql_time = date('G:i:s',$time);

        //SELECT SHIFT CODE THAT APPLIES TO CURRENT PIT//
        $query = "SELECT shift FROM shift_rules WHERE STR_TO_DATE('{$mysql_time}','%H:%i:%S') BETWEEN start_time_24 AND end_time_24 LIMIT 1";
        $current_shift_set = mysqli_query($connection, $query);

        if(mysqli_num_rows($current_shift_set) == 1) {
            $current_shift = mysqli_fetch_assoc($current_shift_set);
            if($current_shift['shift'] == '1'){$shift1_hours++;}
            elseif($current_shift['shift'] == '2'){$shift2_hours++;}
            elseif($current_shift['shift'] == '3'){$shift3_hours++;}
            else{$shift0_hours++;}
        } else {
            $shift0_hours++;
        }
        //INCRIMENT TIME BY 1 MINUTE//
        $time = strtotime("+1 minute",$time);
    }

    $shift1_hours = $shift1_hours/60;
    $shift2_hours = $shift2_hours/60;
    $shift3_hours = $shift3_hours/60;
    $shift0_hours = $shift0_hours/60;

    //UPDATE TIMECARD ROWS WITH SHIFT HOURS//
    $query = "UPDATE source_filtered_timecard 
              SET shift1_time = {$shift1_hours}, 
                  shift2_time = {$shift2_hours},
                  shift3_time = {$shift3_hours},
                  shift0_time = {$shift0_hours}
              WHERE id = '{$record['id']}'";
    $update = mysqli_query($connection, $query);
}