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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
