I have a table called tbl_shift:
enddate endtime startdate starttime
2009-03-03 16:00:00 2009-03-03 08:00:00
2009-03-04 00:00:00 2009-03-03 16:00:00
I need to calculate the total worked hours.
I can do this by selecting the sum of timediff between 'enddate endtime' and 'startdate starttime' (THANKS TO ushastry: http://www.experts-exchange.com/Database/MySQL/Q_24147108.html
ddate, ' ', endtime), CONCAT(startdate, ' ', starttime)))))) / 3600
WHERE startdate = '2009-03-03'
This will give me 16 hours.
My problem is that I need to subtract 30 minutes for each work day, if they worked a full shift (8 hours). So, the above example would need to be 15 hours instead of 16.
I'm using mysql 5.1