6 Replies Latest reply on Nov 27, 2015 9:20 AM by broccolus

    Calculation involving differences between timestamps

    broccolus

      Hi Everybody

       

       

      I'm very new to filemaker and looking to set up a data collection system for a research project.

      I have a table, FLOW_RECORDS, in which I am creating a new record for each time I collect data at each of many sampling sites (referred to by "Site_ID"). These records are timestamped, and I will be visiting each Site_ID approximately every day. In order to calculate flow rates, I need to know how much time has elapsed between records for a given sample site. I have created a field called "time_elapsed" where I am trying to calculate the difference between the automatically generated timestamp and the previous timestamp only for the matching Site_ID. I am having trouble determining what function I should use in the calculation of time_elapsed in order to look only at a subset of my table with matching Site_ID to the current record. My guess was using LookupNext(Timestamp;lower) but I haven't managed to delimit by Site_ID. Am I on the right track? Any ideas for another function which may solve my problem?

        • 1. Re: Calculation involving differences between timestamps

          Hi broccolus,

           

          You could try creating a calculation with a result of Time:

           

          GetAsTime(Table::PreviousTimestamp -Table::GeneratedTimestamp)

           

          This should give you an elapsed time value: HH:MM:SS

          • 2. Re: Calculation involving differences between timestamps
            Mike Duncan

            Hi broccolus,

             

            Here is a function for outputting an ISO 8601 duration, although I only built it to show days, hours and minutes, if that will work for your needs.

             

            Mike

             

             

            Let (

            [

              $this.diff = GetAsTimestamp ( GetAsDate ( table::end_date ) & " " & GetAsTime ( table::end_time ) ) - GetAsTimestamp ( GetAsDate ( table::start_date ) & " " & GetAsTime ( table::start_time ) ) ;

             

              $this.hours = Hour ( $this.diff ) ;

              $this.min = Minute ( $this.diff ) ;

              $this.sec = Seconds ( $this.diff ) ;

             

              end = ""

            ] ;

             

            "P" &

            Case (

              $this.hours > 24 ;

               Floor ( $this.hours / 24 ) & "D" & Mod ( $this.hours ; 24 ) & "H" ;

             

              $this.hours & "H"

             

            )

            &

            Case (

              $this.min > 0;

              $this.min & "M"

            )

             

            )

            • 3. Re: Calculation involving differences between timestamps
              okramis

              This should be quite simple.

              Just subtract the older from the newer timestamp and get it as time:


              GetAsTime ( newerTimeStamp - olderTimeStamp )

              To get your previous timestamp, you could use ExecuteSQL:

               

              Let ( [

              _result = ExecuteSQL ( "

               

              SELECT MAX(COALESCE(YourTimestamp,''))

              FROM YourTable

              WHERE Site_ID = ? AND YourTimestamp < ?

               

              " ; "" ; "" ; YourTable::Site_ID ; YourTable::YourTimestamp )

              ; _result = Substitute ( _result ; " " ; "¶" )

               

              ] ;

              Timestamp ( GetAsDate ( GetValue ( _result ; 1 ) ) ; GetAsTime ( GetValue ( _result ; 2 ) ) )

              )


              here's a test calculation for the data viewer:


              Let ( [

              _nminutes = 60

              ; _now = Get ( CurrentTimeStamp )

              ; _then = Timestamp ( get ( currentdate ) ; get ( currenttime ) + 60 * _nminutes )

              ] ;

              getasTime ( _then - _now )

              )

               

              returns: 01:00:00

               

              regards

              Otmar

              • 4. Re: Calculation involving differences between timestamps
                Mike_Mitchell

                broccolus -

                 

                What I would suggest here is you create a self-joining relationship based on SiteID. On the Relationships Graph, add another table occurrence of the current table. Join the two based on SiteID = SiteID and current timestamp > self::timestamp. This will give you a related set of records consisting of all records matching the current site ID and which have earlier timestamps.

                 

                Then, sort the relationship based on timestamp (descending). This will cause the first related record to be the record immediately preceding the record you're on. Your calculation for elapsed time then becomes:

                 

                Let ( [

                prevVal = FLOW_RECORDS_SELF::timestamp

                ] ;

                 

                 

                Case ( prevVal < timestamp and not IsEmpty ( prevVal ) ;

                timestamp - prevVal ;

                ""

                 

                 

                )

                )

                 

                The attached file shows an example.

                 

                HTH

                 

                Mike

                • 5. Re: Calculation involving differences between timestamps
                  Mike_Mitchell

                  Caution with this technique: Performance. As your record count grows, this is going to start to get slow, especially over a slow network. The < / > operators are going to be a slower than = operators, and re-evaluating the calculation every time someone goes to that screen will eventually be annoying to users. You may find that, at some point, you'll want to use a scripted process to store the value rather than relying on the unstored calculation to pull it down.

                  • 6. Re: Calculation involving differences between timestamps
                    broccolus

                    Thanks Mike!  This worked great, and your example file was very helpful in deciphering the solution for a beginner.