ConvertTimestampToUTC ( inputTS )

Used to convert a given FileMaker timestamp to a ISO8601 Timestamp format

Average rating: 5.0 (1 vote) Log in to vote

Robert D Trammel - Show more from this author
https://www.linkedin.com/in/robertdtrammel/

Sample input:

ConvertTimestampToUTC( GetAsTimestamp( "10/1/2024 5:00:00 PM" ) )

Sample output:

2024-10-01T12:00:00.529Z

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

// The goal is to convert a localized FileMaker Timestamp back into UTC
// Target format is ISO 8601 format including timezone, for example 2016-03-10T18:07:52.534Z
Let([
   modifier = GetAsTime( Get( CurrentHostTimestamp ) - GetAsTimestamp( Get( CurrentTimeUTCMilliseconds ) / 1000 ) );
   FMAsUTC = GetAsTimestamp( inputTS + ( -modifier ) );
   FMAsUTCTime = GetAsTime( FMAsUTC );
   ISODate = Year( FMAsUTC ) & "-" & Right( "0" & Month( FMAsUTC ); 2 ) & "-" & Right( "0" & Day( FMAsUTC ); 2 );
   ISOHour = Right( "0" & Hour( FMAsUTCTime ); 2 );
   ISOMin = Right( "0" & Minute( FMAsUTCTime ); 2 );
   sec = Seconds( FMAsUTCTime );
   ISOSec = Case( 
      Position( sec; "."; 1; 1 ) = 1; "00" & sec; 
      Position( sec; "."; 1; 1 ) = 2; "0" & sec;
      sec
   );
   ISOTime = ISOHour & ":" & ISOMin & ":" & ISOSec
];
   ISODate & "T" & ISOTime & "Z"
)

The goal of this function is that you can give it a timestamp, and it will give you the UTC version of that timestamp. I don't want to have to write this calculation ever again. So I'm sharing it with you all. This will calculate your timezone modifier, apply it to the given timestamp, and then format it in ISO 8601 format. This comes in handy primarily when working with web api's from FileMaker without using some kind of web viewer to format the date. If you have ideas for improvements or a better way of doing it, please let me know in the comments.

NOTE: If you just want the FileMaker date in UTC without converting to ISO 8601, you can just do this.
Let([
modifier = GetAsTime( Get( CurrentHostTimestamp ) - GetAsTimestamp( Get( CurrentTimeUTCMilliseconds ) / 1000 ) );
];
GetAsTimestamp( inputTS + modifier )
)

I hope this helps someone. Thanks.

Comments

		Robert D Trammel Oct 22, 2024
Or even without a let statement: GetAsTimestamp( inputTS + ( GetAsTime( Get( CurrentHostTimestamp ) - GetAsTimestamp( Get( CurrentTimeUTCMilliseconds ) / 1000 ) ) ) )

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

ConvertTimestampToUTC ( inputTS )

Comments

Support this website.

Support this website.