3 Replies Latest reply on Dec 2, 2014 8:05 AM by TSGal

    Can you total time fields using Execute SQL

    thehalpeen

      Summary

      Can you total time fields using Execute SQL

      Product

      FileMaker Pro

      Version

      13

      Operating system version

      Window 7

      Description of the issue

      I can add up an amount field which is a number field - sum(amount) using Execute SQL. Is it possible to add up a set of records that contain times using Execute SQL - at the moment sum (time) returns an ?

        • 1. Re: Can you total time fields using Execute SQL
          TSGal

          Michael Lally:

          Thank you for your post.

          A Time field is a point in time; not an interval or duration.  Therefore, you would need to find the duration and adjust from there.  For example, assuming you want to find the duration in minutes:

          SELECT SUM(DATEDIFF(MINUTE, '0:00:00', time ) ) FROM <table>

          If you are going to be grouping by an individual, then at the end, include:  ....  WHERE GROUP BY <Individual ID>

          Let me know if you need additional clarification.

          TSGal
          FileMaker, Inc.

          • 2. Re: Can you total time fields using Execute SQL
            greglane

            Hi TSGal,

            Does the DATEDIFF function really work with FileMaker? I can't find it in the FileMaker 13 SQL Reference and I've never had success with it in FileMaker.

            In this case, I think the same result could be achieved with something like:

            SELECT SUM( NUMVAL( myTimeField )) FROM myTable

            Thanks,

            Greg

            1 of 1 people found this helpful
            • 3. Re: Can you total time fields using Execute SQL
              TSGal

              greglane and Michael Lally:

              My apologies.  DATEDIFF is not a supported SQL function in FileMaker Pro.  NUMVAL is supported, and this will return a numeric value in seconds.  For example, if there are two records in the table with Time values of 0:02:31 and 0:03:51, the result would be 382.  You can then use a calculation to put this into the value 0:06:22.

              TSGal
              FileMaker, Inc.

              1 of 1 people found this helpful