GetAsFMTimestamp_Offset ( source ; HrsOffset ; Include_Seconds )

Similar to my other Function GetAsFMTimestamp, but provides 2 additional parameters

Be the first to rate this function Log in to vote

Kyle Williams - Show more from this author
Augusto Digital
https://www.youtube.com/channel/UCpJlB7cmVNMjbjgY96MEsng

Sample input:

GetAsFMTimestamp_Offset( "2019-05-08T00:16:34.933"; 1; 1 ) //UTC (+1hr)
GetAsFMTimestamp_Offset( "2019-05-08T00:16:34.933"; -1; 1 ) //UTC (+1hr)
GetAsFMTimestamp_Offset( "2019-05-08T00:16:34.933"; 1; 0 ) //UTC (+1hr & no seconds)

Sample output:

5/8/2019 1:16:34 AM
5/7/2019 11:16:34 PM
5/8/2019 1:16 AM

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

//Author:  Kyle Williams (8/12/2019)
//Last Updated:  4/13/2022 - Added support for millisecond timestamps
//Dependent on CF "TimeFormatAs": https://www.briandunning.com/cf/144

/*
Supported TS formats:
	UTC:  "2019-05-08T00:16:34.933"
	Unix:  "1559412000"
	FM_TimeStamp: "8/8/2019 10:15:26 PM"
	JSONDate:  "2019-05-08"
	FM_Date:  "8/29/2019"
	UTC_Simplified:  "20190310T150000"
*/

Let([
//Sample entries for testing:
//	HrsOffset = -0;
//	source = "8/8/2019 10:15:26 PM";
//	Include_Seconds = 0;

//Start of Custom Function:
source = GetAsText(source);
IsValidTS = not (GetAsTimestamp ( source ) = "?");

//Test if TS is in UTC format:
_1_4 = Middle ( source ; 1 ; 4 );//UTC YEAR
_5 = Middle ( source ; 5 ; 1 );
_5_6 = Middle ( source ; 5 ; 2 );//UTC_Simplified MONTH
_6_7 = Middle ( source ; 6 ; 2 );//UTC MONTH
_7_8 = Middle ( source ; 7 ; 2 );//UTC_Simplified DAY
_8 = Middle ( source ; 8 ; 1 );
_9 = Middle ( source ; 9 ; 1 ); // test if "T"
_9_10 = Middle ( source ; 9 ; 2 );//UTC DAY
_10_11 = Middle ( source ; 10 ; 2 );//UTC_Simplified HRS(24)
_12_13 = Middle ( source ; 12 ; 2 );//UTC_Simplified MINUTES
_14_15 = Middle ( source ; 12 ; 2 );//UTC_Simplified SECONDS
_11 = Middle ( source ; 11 ; 1 );
_12_19 = Middle ( source ; 12 ; 8 );//UTC TIME
_14 = Middle ( source ; 14 ; 1 );
_16 = Middle ( source ; 16 ; 1 );//UTC_Simplified Zulu Indictor
_17 = Middle ( source ; 17 ; 1 );



isUTC = (_5 & "|" & _8 & _11 & _14 & _17 = "-|-T::");

//Test if TS is in Unix format
isUnix = Let( digits = Filter ( source ; "0123456789" ); Length(digits) = Length ( source ));
isJSONDate  = (_5 & "|" & _8 = "-|-");
isUTC_Simplified =  (_9 & Length ( source ) = "T15") or (_9 & Length ( source ) = "T16");  //16 if last char = "Z" for Zulu time.
isTimeUTCMilliseconds = (source > 63774148031946) and (Length( Filter( source; "1234567890" )) = Length ( source ));

NewTS = 
Case( 
isTimeUTCMilliseconds; 
	 Let ([
		 UTC = source //similar to result from: Get ( CurrentTimeUTCMilliseconds )
		;OffsetTimeFromUTC = ""
		;ts = GetAsTimestamp ( GetAsNumber ( Left ( UTC ; Length ( UTC ) - 3 ) ) )
		;ms = GetAsNumber ( Right ( UTC ;  3 ) ) * .001
		;ts_ms = ts + ms
		;offset =
		 Case (
		IsValid ( GetAsTime ( OffsetTimeFromUTC ) ) and (GetAsTime ( OffsetTimeFromUTC ) < GetAsTime ( "12:00" )) ;
			GetAsTime ( OffsetTimeFromUTC )

		;GetAsNumber ( OffsetTimeFromUTC ) ≥ 100 and not PatternCount ( OffsetTimeFromUTC ; ":" ) ;
			Time ( 
				Left ( Right ( 0 & GetAsNumber ( OffsetTimeFromUTC ) ; 4 ) ; 2 ) ;
				Right ( GetAsNumber ( OffsetTimeFromUTC ) ; 2 ) ;
				0
				);
			0)
		;ts_ms_offset = ts_ms + offset
	];
		ts_ms_offset
	);
IsValidTS; 
	GetAsTimestamp ( source ); 
IsUTC;
	GetAsTimestamp( _6_7 & "/" & _9_10 & "/" & _1_4 & " " & TimeFormatAs ( _12_19; 12) ); 
isUnix;
	GetAsTimestamp ( (GetAsNumber ( Left(source;10) ) )
    + GetAsNumber (Timestamp ("01/01/1970" ; "00:00:00"))
    + GetAsNumber ( Get(CurrentTimestamp) )
            //Adjust for local timezone:
    - Floor ( Get(CurrentTimeUTCMilliseconds)/1000) );  
isJSONDate;
GetAsTimestamp( _6_7 & "/" & _9_10 & "/" & _1_4 & " " & TimeFormatAs ( _12_19; 12) );
isUTC_Simplified; 
	GetAsTimestamp( _5_6 & "/" & _7_8 & "/" & _1_4 & " " & TimeFormatAs ( _10_11 & ":" & _12_13 & ":" & _14_15; 12) ); 
"Unknown Timestamp"
)
];
Case(
	Include_Seconds;
		NewTS + (3600 * HrsOffset);
	GetAsTimestamp ( Floor ( (NewTS + (3600 * HrsOffset))/60)*60)
)
)

This is probably one of my favorite functions and the one I'm most proud of creating as it is extremely useful. So many API integrations use a different form of timestamp and this allows you to use one simple function to transform all of the different various Timestamp formats into a FileMaker formatted timestamp that can be combined with the GetAsDate and GetAsTime functions.

Comments

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

GetAsFMTimestamp_Offset ( source ; HrsOffset ; Include_Seconds )

Comments

Support this website.

Support this website.