You could try creating a calculation with a result of Time:
This should give you an elapsed time value: HH:MM:SS
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.
$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 = ""
$this.hours > 24 ;
Floor ( $this.hours / 24 ) & "D" & Mod ( $this.hours ; 24 ) & "H" ;
$this.hours & "H"
$this.min > 0;
$this.min & "M"
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 ( "
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 )
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.
timestampCalc.fmp12.zip 67.8 K
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.
Thanks Mike! This worked great, and your example file was very helpful in deciphering the solution for a beginner.