<[EMAIL PROTECTED]> wrote on 10/24/2005 12:48:32 AM: > > 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' The only issue will be if there is a mismatch between # of clock-ins and # of clock-outs (person is currently on the clock or they checked out on the following day, perhaps). That's something you are going to need to build a little logic to handle but this should help you get started. Shawn Green Database Administrator Unimin Corporation - Spruce Pine