Using ESQL to SUM() a time field?

[Justin Close]

I have a query that I am trying to get to aggregate some time data, so want to do a SUM() on a Time field.  Data in the field is a duration (i.e. NOT a time of day) in MM:SS format, and I am trying to calculate how long something took to process.  (The table has a variety of records for the same parent object, representing various stages of work, so want to find out the total amount of time put in on a parent object.)

 

I am having trouble getting it to work,  just getting question marks in response.  Things work well with normal FM calculations. 

 

Here are a few of the things that I have tried:

ExecuteSQL (
    "SELECT SUM ( MovieRawTime_AsTime )
     FROM Movies
     WHERE MovieEditingDate > ? and MovieEditingDate < ?   "
; "" ; "" ; Date_Start ; Date_End )

Trying to convert it to raw # of seconds for summing, then would convert it back (that part isn't shown):

ExecuteSQL (
    "SELECT DATEDIFF ( MINUTE, '00:00:00', MovieRawTime_AsTime )
     FROM Movies
     WHERE MovieEditingDate > ? and MovieEditingDate < ?   "
; "" ; "" ; Date_Start ;Date_End )

Any suggestions would be appreciated,

-- J

[mr_vodka]

Well you probably shouldn't be using a time field for something like this.

 

That being said, you may be able to convert it from one type to another. Try something like:

Let ( tt = ExecuteSQL ( "
                          SELECT SUM ( CAST ( MovieRawTime_AsTime AS INT ) )
                          FROM Movies
                          WHERE MovieEditingDate > ? and MovieEditingDate < ?
                        "
                        ; ""; ""; Date_Start; Date_End
                      );

      GetAsTime ( GetAsNumber ( tt ) )
    )