List: General Discussion « Previous MessageNext Message » From: Martijn Tonies Date: October 24 2005 1:37pm Subject: Re: sum of time? View as plain text
```> > 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.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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