addtime with more than two and mixed argument

I am making a work time tracking database that开发者_如何学Python needs to do some calculating on work hours as well.

The most simple operation is possible through.

AddTime(`starttime`,-`endtime`)

But as soon as I need to calculations on more numbers I fail.

Say the work can span over midnight and starttime: 20:00:00 and endtime 06:00:00 i would do something like:

if(`starttime`<`endtime`, AddTime(`endtime`,-`starttime`),AddTime(`endtime`, AddTime (-`starttime`,24:00:00))) FROM tid WHERE 1

But that isn´t accepted.

And when I need to add in the possibility for a lunch break it gets even more complicated.

What to do?

To keep your calculations simple, I'd suggest that along with start and end times, you also store start and end dates, in the same field. Doing so, you can find the working hours by simply doing the below in your MySQL query:

TIMESTAMPDIFF(HOUR, `start_datetime`, `end_datetime`);