<[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
