Calculating Total Time 1

[NorthStarDA]

I am using MySQL 4.0.17-standard on W2K and I am having trouble with a time management application. It's actually very simple, I have 2 columns TIME_IN and TIME_OUT. They are both time fields, and i need to get a TOTAL_TIME column from a query. I know that there is a TIMEDIFF function in MySQL 4.1 but I am not in a position to upgrade right now. This is a quite simple task and I imagine that there is way to do it, I just can't figure it out.

thanks

 

[TonyGroves]

You could use TIME_TO_SEC(time) to convert the times to seconds, and subtract them.

 

[TonyGroves]

... and then use SEC_TO_TIME to convert back into h:m:s.

 

[NorthStarDA]

Thanks Tony!

I see how to use the time_to_sec and sec_to_time functions but how to do the math? I thought that in order to use math functions in mysql, you had to have a GROUP BY in your query. Is this not true? Im still new to more complex queries.

Thanks

 

[TonyGroves]

It's not really complex at all. You could try:

SELECT
SEC_TO_TIME(TIME_TO_SEC(time_out)-TIME_TO_SEC(time_in)) TOTAL_TIME,
other_fields
FROM table

 

[sleipnir214]

NorthStarDA:
You don't have to use a GROUP BY clause for all math functions. Only for the so-called aggregating functions that derive their returned values by examining more than one row. Examples are count(), avg(), min(), max().

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[NorthStarDA]

thanks everyone!

if i throw in a GROUP BY, will that total the time properly? here is an example:

SELECT project_id,employee_id,work_date,action,time_in,time_out,
SEC_TO_TIME(TIME_TO_SEC(time_out)-TIME_TO_SEC(time_in)) TOTAL_TIME
FROM work_history
WHERE employee_id = #session.employee_id#
AND work_date BETWEEN #CreateODBCDate(TheWeekStart)# AND #CreateODBCDate(TheWeekEnd)# GROUP BY work_date

i want to show a total time worked for each day of the week. this query is not working right as is.

thanks to all for your quick responses!

 

[TonyGroves]

The following should work, assuming you want to group by project/employee/date. Note the inclusion of the SUM() function.

SELECT
project_id,
employee_id,
work_date,
SEC_TO_TIME(SUM(TIME_TO_SEC(time_out)-TIME_TO_SEC(time_in))) TOTAL_TIME
FROM
work_history
WHERE
employee_id = #session.employee_id#
AND work_date BETWEEN #CreateODBCDate(TheWeekStart)# AND #CreateODBCDate(TheWeekEnd)#
GROUP BY project_id,employee_id,work_date

 

[NorthStarDA]

awesome, works beautifully!

thanks!!

 

[NorthStarDA]

hello again,

i am having very mysterious errors (intermittently) when running this query through coldfusion- to the point that I am working with a macromedia engineer to try to figure it out, but we can't. Anyways, I have upgraded my data server to 4.1.1 and I have the timediff function, which appears to be working well, do you know of a way I can still group by project in this query?

SELECT work_history.project_id,work_history.time_in,work_history.time_out,projects.ID, 
TIMEDIFF(work_history.time_out,work_history.time_in) AS TOTAL_TIME 
FROM work_history,projects 
WHERE work_date BETWEEN {d '2004-03-15'} AND {d '2004-03-24'} 
AND projects.ID = work_history.project_id

i would like to group by ID and SUM the TOTAL_TIME column, however when i do, the sum doesn't appear to work right- is there an easy way to do this with the newer version of mysql?

 

[r937]

yes, because you still cannot SUM times

again, you'd want to convert the result of the TIMEDIFF function to seconds, SUM them up as seconds, then convert back to time

select work_history.project_id
     , work_history.time_in
     , work_history.time_out
     , sec_to_time(
         sum(
           time_to_sec(
              timediff(work_history.time_out
                     , work_history.time_in)
                      )
            )     )      as total_time 
  from work_history
inner
  join projects 
    on work_history.project_id
     = projects.ID 
 where work_date between '2004-03-15'
                     and '2004-03-24' 
group
    by work_history.project_id
     , work_history.time_in
     , work_history.time_out

rudy

http://r937.com/

SQL Consulting