• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1815
  • Last Modified:

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

0
Rick
Asked:
Rick
2 Solutions
 
oobaylyCommented:
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_LambertusCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now