|Subject||Re: calculating sum of times|
> I have the following tableThis depends on the field definition of Time1 and Time2. If it is
> ID Time1 Time2 TotalTime
> Time1 and Time2 are posted by stored procedure. Then
> in the end of processing i am doing
> UPDATE MyTable SET TotalTime = Time2 - Time1
> Am i right to assume that TotalTime will now hold the amount
> of time passed since Time1 till Time2?
Time, then it will be the difference between the time, and times
spanning midnight may not give you the result you expect. If it is
timestamp, then it will return to you the number of days between the
two times, so 6 hours would be represented as 0.25 etc.
>select (sum(TotalTime)*24) as NumHours
> Now...I need to calculate SUM(TotalTime) for all records in a table..
> And get the hours...How do i go about that?
you may need to play around with casting to get it in hour minute
format or decimal hour format depending on what you are after.
But can I make a recommendation here.
Being a simple calculation that does not involve running queries on
other tables, if you have no intention of indexing the TotalTime, just
create it as a computed field. Computed fields are very slow in some
cases, but this should be fine.
If you want it to be a real field, forget running that query in your
stored procedure, you are far better off creating a trigger that
calculates the value on insert or update. Doing it the way you are
doing it risks someone some time in the future forgetting to update
the totaltime field, and that may stuff something else up.