> > I have a table doing time tracking. I don't use timestamps, I use > > datetime fields to record punch in times and punch out times. I > > have this query that computes the amount of time between a punch in > > and punch out: > > > > select SEC_to_time(unix_timestamp(TTendTime) - > unix_timestamp(TTstartTime)) > > as endtime > > FROM TimeTracking > > WHERE TTperson = 1 > > and date(TTstartTime) = '2005-10-19' > > > > And this works great except for when people punch in and out several > > times in one day. Is there any way I can total a number of records > > into one total time? In this example case, I am TTperson #1 and I > > punched in and out five times this day. > > > > I know I can do it in the code, but if I can do it in sql, life > > would be better for me. > > > > --ja > > > > -- > > Let's do some basic algebra: > > et = end time > st = start time > > Total time = (et1 - st1) + (et2 - st2) + ... + (etN - stN) > Where N = how many clock-ins and clock outs they had. > > We can rewrite this as > > TT = et1 - st1 + et2 - st2 + ... etN - stN > = et1 + et2 + ... etN - st1 - st2 - ... - stN > = et1 + et2 + ... etN - (st1 + st2 + ... + stN) > = SUM(et(1..N))-SUM(st(1..n)) > > This translated to SQL as: > > select SEC_to_time(SUM(unix_timestamp(TTendTime)) - > SUM(unix_timestamp(TTstartTime))) > as endtime > FROM TimeTracking > WHERE TTperson = 1 > and date(TTstartTime) = '2005-10-19' >
Won't this fail if it overflows 24 hours? Given that the result of SEC_TO_TIME seems to be a valid "time value", which "duration" is not? eg: a duration can be 25 hours long, while a TIME value cannot. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
