RoundTS

Round a timestamp value to nearest minute value defined by seconds

Be the first to rate this function Log in to vote

Jeff Morrison - Show more from this author

Sample input:

RoundTS ( Get ( CurrentHostTimestamp ) ; 300 )
where current timestamp is 1/1/2023 02:03:33

Sample output:

1/1/2023 02:05:00

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*  Receives a timestamp, then returns a timestamp rounded to the nearest time based on the 'secondstoround' parameter.
Using 60 rounds to the nearest minute
Using 300 rounds to the nearest 5 minute mark
Using 900 rounds to the nearest 15 minute mark
For example:  
With 'secondstoround' set to 300, 1/1/2023 01:03:00 would return 1/1/2023 01:05:00
With 'secondstoround' set to 900, 1/1/2023 01:03:00 would return 1/1/2023 01:00:00 while 1/1/2023 01:08:00 would return 1/1/2023 01:15:00
Uses a single case statement. Since a case statement stops once it reaches a line that is true, we test at 3 minutes, then 8, etc.
Less than 3 rounds to 0, less than 8 rounds to 5, less than 13 rounds to 10, etc.   
*/


If(
GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / secondstoround ; 0 ) * secondstoround ) = "24:00:00"; Date ( Month ( mytimestamp ) ; Day ( mytimestamp ) ; Year ( mytimestamp ) ) + 1 & " " & "12:00:00 AM"; 

Date ( Month ( mytimestamp ) ; Day ( mytimestamp ) ; Year ( mytimestamp ) ) & " " & 

Case(
Hour(GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / secondstoround ; 0 ) * secondstoround ))=0; 12; 
Hour(GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / secondstoround ; 0 ) * secondstoround )) > 12; Hour(GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / secondstoround ; 0 ) * secondstoround )) - 12; 
Hour(GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / secondstoround ; 0 ) * secondstoround ))) & 

":" & Right("00" & Minute(GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / secondstoround ; 0 ) * secondstoround )); 2) & ":00" &

If(Hour(GetAsTime( Round( GetAsTime( Time ( Hour ( mytimestamp ) ; Minute ( mytimestamp ) ; Seconds ( mytimestamp ) ) ) / secondstoround ; 0 ) * secondstoround )) >= 12; " PM"; " AM")
)

Call this function and hand it 2 parameters: timestamp & number representing seconds to round.

For example:
Insert Calculated Result [Select ; TableName::FieldName; RountTS ( Get ( CurrentHostTimestamp) ; 300 )]

This would get the current host timestamp, round it using the 300 seconds (5 minutes) then insert that value into the chosen field.

Comments

		Michael Horak, *COMMENT Visual Realisation Mar 17, 2023
How about simply: GetAsTimestamp ( Round ( given_timestamp / precision ; 0 ) * precision ) The value of the precision parameter can be given as time or as a number of seconds.

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

RoundTS

Comments

Support this website.

Support this website.