Kaboom Kaboom - 1 year ago 41
MySQL Question

Generate time between events giving wrong info

The Issue

In our calendar users can make events that will load into a MySQL database. The start time is stored and the end time is stored. In the database they are stored like this

start_time_first, start_time_second, end_time

`9`, `30`, `11:00` (9:30 - 11:00)
`13`, `10`, `14:25` (1:10 - 2:25)

I then split the variables up when loaded to give me 4 variables which all works like expected using the following:

$real_time_start = $time_start[0] . $time_start[1]; // gives something like 930
$real_time_end = $time_end[0] . $time_end[1]; // gives something like 1100
$difference = $real_time_end - $real_time_start; // find the difference
// pad the difference for 30 minutes
// and then format it for display
$difference = str_pad($difference, 3, '0', STR_PAD_LEFT);
$difference = $difference[0].":".substr($difference, -2, 2);

The obvious issue I am having is that if the
event is
and the start is
that when I subtract them the value I get is 70 instead of 30. If the
is lower than the
then it will give me a bad value that doesn't work for time.

Here is a visual representation. As you can see the one value is correct while the other... not so much:

enter image description here

Is there a better way to simulate time between the two events in the way I am doing it or a better math I am missing to fix this issue? I had a go at writing a long way around it but it's much more difficult to get every occurrence the way I had written it. Hopefully this can help someone else in the future as well.

The database structure

enter image description here

Answer Source

You can't use the decimal system to subtract day times because minutes flow from 60 to the next hour and not from 100. PHP provides the DateTime/DateInterval classes for this:

$start=new DateTime('9:30');
$end=new DateTime('11:00');

(This is just to demonstrate; theoretically this can be problematic with DST, so if you can you should include the date as well to have an exact solution.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download