MySql - Select the Sum of Timediff

Hello,

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)

Like this:

SELECT ((SUM(TIME_TO_SEC(TIMEDIFF(CONCAT(enddate, ' ', endtime), CONCAT(startdate, ' ', starttime))))))  / 3600
FROM tbl_shift
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


Thank you,
Rick

LVL 13
RickAsked:
Who is Participating?
 
oobaylyConnect With a Mentor Commented:
You could create a function to accept the date & time limits, then just sum the responses:
SELECT SUM(GET_WORKING_HOURS(startdate, starttime, enddate, endtime))
FROM tbl_shift
WHERE startdate = '2009-03-03'
 
 
-- Function
CREATE DEFINER=`root`@`localhost` FUNCTION `GET_WORKING_HOURS`(startdate CHAR(10), starttime CHAR(8), enddate CHAR(10), endtime CHAR(8)) RETURNS float
    NO SQL
BEGIN
 
DECLARE start DATETIME;
DECLARE finish DATETIME;
DECLARE delta FLOAT;
 
SET start = CAST(CONCAT(startdate, ' ', starttime) AS DATETIME);
SET finish = CAST(CONCAT(enddate, ' ', endtime) AS DATETIME);
 
SET delta = TIME_TO_SEC(TIMEDIFF(finish, start)) / 3600;
 
IF (delta >= 8) THEN
  SET delta = delta - .5;
END IF;
 
RETURN delta;
 
END

Open in new window

0
 
Tomas_LambertusConnect With a Mentor Commented:
I think oobayly's method is the best way to do it, but if you don't have permissions or don't want to go trough the trouble of defining a function this should return the same result:


SELECT SUM(IF(hours.t<28800,hours.t,hours.t-1800))/3600
FROM (
  SELECT TIME_TO_SEC(TIMEDIFF(CONCAT(enddate, ' ', endtime), CONCAT(startdate, ' ', starttime))) as t
  FROM tbl_shift
  WHERE startdate = '2009-03-03'
) hours;

Open in new window

0
 
RickAuthor Commented:
Thanks to both of you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.