php - 24 hours schedule- Time Diff

Answer

--------+-----+

Answer

--+

Answer

+

Answer

-+ | name | day | start_time | end_time | total | +

Answer

--------+-----+

Answer

--+

Answer

+

Answer

-+ | Test01 | fri | 06:00:00 | 15:00:00 | 09:00:00 | | Test | fri | 07:00:00 | 16:00:00 | 09:00:00 | | Test02 | fri | 08:00:00 | 14:30:00 | 06:30:00 | | Pablo Quesada | fri | 22:00:00 | 01:30:00 | -20:30:00 | +

Answer

--------+-----+

Answer

--+

Answer

+

Answer

-+

Answer

Solution:

I think acase statement does what you need:

select (case when start_time < end_time
             then timediff(end_time, start_time)
             else timediff(addtime(end_time, '24:00:00', start_time))
        end)
Copy code

Answer

Solution:

You can use a simple if-statement:

SELECT IF( end_time > start_time,
           end_time - start_time,
           end_time + 240000 - start_time ) as total
FROM timetable;
Copy code

Answer

Solution:

You can use PHP's time function for this task.time() returns the number of seconds since 1/1/1970.

Store start time as (example)

$startTime = time() + (2 * 3 * 20 * 43); // now + 2days, 3hrs, 20mins; 43secs
Copy code

and their end time to some time after that. If you subtract their start time from their end time the result will always be a positive number of seconds that they spent working. You also won't need the day column since the $time function returns the number of seconds since 1/1/1970, so you have the date there as well, for free.

In your UI, you can then format the resulting number of seconds to a usable time and date using PHP's date function.

Answer

Solution:

Thanks for your feedback guys. With your help, I did it as follows:

select IF( end_time > start_time, TIMEDIFF (end_time,start_time), TIMEDIFF (addtime(end_time,'24:00:00') ,start_time)) as Total_Hours from timetable;