List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 24 2005 1:17pm
Subject:Re: sum of time?
View as plain text  
<jabbott@stripped> 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
Thread
sum of time?jabbott24 Oct
  • Re: sum of time?Dobromir Velev24 Oct
    • mysql + LVSWinn Johnston24 Jul
      • RE: mysql + LVSJimmy Guerrero24 Jul
        • RE: mysql + LVSWinn Johnston25 Jul
          • RE: mysql + LVS highjacked (mysql + NFS ramfs)Winn Johnston25 Jul
            • RE: mysql + LVS highjacked (mysql + NFS ramfs)Barry Newton25 Jul
  • Re: sum of time?SGreen24 Oct
    • Re: sum of time?Martijn Tonies24 Oct
      • Re: sum of time?Michael Stassen24 Oct
        • Re: sum of time?Michael Stassen24 Oct
    • Re: sum of time?Martijn Tonies24 Oct
    • Re: sum of time?Martijn Tonies24 Oct
      • Re: sum of time?SGreen24 Oct
        • Re: sum of time?jabbott24 Oct