Logic to display data - Slice days

Question

Logic to display data - Slice days

Navigation

#1 by (2 votes)

1

Good evening.

Using PHP, I search for 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, they would logically return all 3 rows.

Note that the first record starts on 08, at 11:28:40, and ends the same day at 17:52:12.

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

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

My question would be to "slice" the second record so that the query output looks like:

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 line returned, there is no "overflow" from the time to the next day, but rather "fatie", indicating each specific day.

The ID does not matter in the final result. I tried 3 ways, but either added days beyond what was expected, or did not complete the times as it should.

All tips will be welcome, whether solving via PHP, or directly in the SQL query. Hugs.

php mysql

asked by anonymous 11.05.2017 / 01:21

1 answer

print matrix in phyton How many percent N item represents in the grand total - Query

score 2 · Accepted Answer

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 at Ideone .

Explanation

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

Create an object of type DateTime for each date, both initial and final;

If the difference between dates is less than or equal to zero, nothing is done with the range, but if it is greater than zero, proceed the algorithm;

Create a array to store all the slices of the range;

As long as the difference between dates in days is greater than zero, do:

4.1. Add to the slice list the slice referring to the start date until the 23:59:59 time of the same day;

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

When the difference in days is zero, it defines the last slice of the interval, between the start date, which will be the 00:00:00 time from the last day to the end date;

li>

Replace in original list of ranges by adding slice list;

Result

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 running 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 interval defined in the database represents a range of more than one day, it is replaced by a list of slices that make up the range.