From: Date: October 24 2005 3:55pm
Subject: Re: sum of time?
List-Archive: http://lists.mysql.com/mysql/190753
Message-Id: <435CE7DE.6050104@verizon.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Martijn Tonies wrote:
>>>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.

First, it is hard to imagine how that would happen given the OP's situation.

Second, no, it won't overflow:

   mysql> SELECT SEC_TO_TIME(60*60*24*5);
   +-------------------------+
   | SEC_TO_TIME(60*60*24*5) |
   +-------------------------+
   | 120:00:00               |
   +-------------------------+
   1 row in set (0.00 sec)

   mysql> SELECT SEC_TO_TIME(60*60*24*50);
   +--------------------------+
   | SEC_TO_TIME(60*60*24*50) |
   +--------------------------+
   | 1200:00:00               |
   +--------------------------+
   1 row in set (0.00 sec)

SEC_TO_TIME() is not limited to 24 hours.

Michael