Solved

# MySql - Select the Sum of Timediff

Posted on 2009-04-24
1,688 Views
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
Question by:Rick

LVL 15

Accepted Solution

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

Assisted Solution

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;
``````
0

LVL 13

Author Closing Comment

Thanks to both of you!
0

## Featured Post

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Creating and Managing Databases with phpMyAdmin in cPanel.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of demâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦