Logic to display data - Slice days

Question

Good night.

Using PHP, I search data with Mysql. The table has the following structure:

ID |      dataHoraInicio     |      dataHoraTermino    |
1  |   2017-05-08 11:28:40   |   2017-05-08 17:52:12   |
2  |   2017-05-08 18:34:02   |   2017-05-10 09:02:57   |
3  |   2017-05-10 09:44:31   |   2017-05-10 13:22:10   |

Assuming you only have these 3 records in the table, in a simple query, without WHERE, would logically return the 3 rows.

Note that the first entry starts on the 8th at 11:28:40 and ends on the same day at 17:52:12.

The second entry starts on the same day as record 1, but ends only two days later, at 09:02:57 on day 10.

The third record starts and ends on the same day as the first record.

My question would be to "slice" the second record, so that the output of the query is as:

ID |      dataHoraInicio     |      dataHoraTermino    |
1  |   2017-05-08 11:28:40   |   2017-05-08 17:52:12   |
2  |   2017-05-08 18:34:02   |   2017-05-08 23:59:59   | <-- hora final alterada
2b |   2017-05-09 00:00:00   |   2017-05-09 23:59:59   | <-- dia "fatiado"
2c |   2017-05-10 00:00:00   |   2017-05-10 09:02:57   | <-- hora inicial alterada
3  |   2017-05-10 09:44:31   |   2017-05-10 13:22:10   |

The idea is that in each returned line, there is no "overflow" of the time for the next day, but rather that it "fatigues", indicating each specific day.

The ID does not matter in the final result. I tried 3 ways, but either add days beyond the expected, or do not complete the schedules as it should.

Any tip will be welcome, either solving via PHP, or directly in the SQL query. Hugs.

Answer 1

Solution

// Intervalos vindos do banco de dados:
$dates = [
    ["2017-05-08 11:28:40", "2017-05-08 17:52:12"],
    ["2017-05-08 18:34:02", "2017-05-10 09:02:57"],
    ["2017-05-10 09:44:31", "2017-05-10 13:22:10"]
];

// Percorre os intervalos de datas:
foreach ($dates as $index => $date)
{

    // (1) Cria um objeto DateTime para cada:
    $start = new DateTime($date[0]);
    $end   = new DateTime($date[1]);

    // (2) Se a diferença entre as datas for superior a um dia:
    if ($end->diff($start)->format("%a") > 0)
    {
        // (3) Lista de fatias do intervalo:
        $interval = [];

        // (4) Enquanto a diferença for maior que um dia:
        while ($end->diff($start)->format("%a") > 0)
        {
            // (4.1) Define a fatia do intervalo para o dia:
            $sliceStart = clone $start;
            $sliceEnd   = clone $start;

            $interval[] = [$sliceStart->format("Y-m-d H:i:s"), $sliceEnd->setTime(23, 59, 59)->format("Y-m-d H:i:s")];

            // (4.2) Incrementa em um dia a data de início:
            $start->modify("+1 day")->setTime(0, 0, 0);
        }

        // (5) Define o intervalo para o último dia:
        $interval[] = [$start->format("Y-m-d H:i:s"), $end->format("Y-m-d H:i:s")];

        // (6) Substitui o intervalo original pela lista de fatias:
        $dates[$index] = $interval;
    }
}

print_r($dates);

See working on Ideone.

Explanation

The variable $dates stores a list of ranges with dates coming from the database. The first value refers to the start date and the second to the end date. With a loop of repetition one iterates over these intervals, making:

1. Creates an object of type DateTime for each date, both initial and final;

2. If the difference between the dates is less than or equal to zero, nothing is done with the range, but if it is more than zero, continue the algorithm;

3. Creates a array to store all slices of the range;

4. While the difference between dates in days is greater than zero, do:

   4.1. Add to the list of slices the slice for the start date up to time 23:59:59 of the same day;

   4.2. Modifies the start date by incrementing one day and returning the time to 00:00:00;

5. When the difference in days is zero, set the last slice of the interval between the initial date, which will be the time 00:00:00 of the last day, until the final date;

6. Replace in the original range list by adding the slice list;

Upshot

The initial range list:

$dates = [
    ["2017-05-08 11:28:40", "2017-05-08 17:52:12"],
    ["2017-05-08 18:34:02", "2017-05-10 09:02:57"],
    ["2017-05-10 09:44:31", "2017-05-10 13:22:10"]
];

After executing the code, it results in:

$dates = [
  ['2017-05-08 11:28:40', '2017-05-08 17:52:12'],
  [
    ['2017-05-08 18:34:02', '2017-05-08 23:59:59'],
    ['2017-05-09 00:00:00', '2017-05-09 23:59:59'],
    ['2017-05-10 00:00:00', '2017-05-10 09:02:57']
  ],
  ['2017-05-10 09:44:31', '2017-05-10 13:22:10']
];

That is, when the range defined in the database represents an interval of more than one day, it is replaced by a list of slices that make up the range.