# php - 24 hours schedule- Time Diff

43

I have to build a 24 hours schedule for all personnel distributed around the globe.

So, for example, somebody can start working at 10:00 and end working at 19:00, so it is very simple to get the time difference since the end time and start time are on the same day. But, if I have somebody who starts working at 19:00 and ends at 04:00 on next day, I will get a negative time difference.

I need to do this continuously, not date specific but it has to repeat weekly. Can you please give me an idea what is the best way to do this?

``````+
``````
880

--------+-----+
651

--+
548

+
518

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

--------+-----+
605

--+
580

+
705

-+ | 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 | +
665

--------+-----+
783

--+
437

+
109

-+
81

#### Solution:

I think a`case` statement does what you need:

``````select (case when start_time < end_time
then timediff(end_time, start_time)
end)
``````
43

#### 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;
``````
795

#### 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
``````

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.

30

#### 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;

People are also looking for solutions to the problem: PHP Built-In Server Can't cURL

Source