GetAsFMTimestamp ( source )

Converts any date or timestamp (including Unix and ISO) to a valid FileMaker formatted timestamp

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 (Get(CurrentDate)) // "9/12/2019"
GetAsFMTimestamp ( "9/12/2019 12:00" ) // Incomplete timestamp
GetAsFMTimestamp (2019-09-12T00:00:00Z")// UTC Timestamp
GetAsFMTimestamp ( 1568320785 ) // Unix Timestamp

Sample output:

9/12/2019 12:00 AM
9/12/2019 12:00 PM
9/12/2019 12:00 AM
9/12/2019 1:39:45 PM

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:
	ISO_Timestamp:  "2019-05-08T00:16:34.933"
	Unix:  "1559412000"
	FM_Timestamp: "8/8/2019 10:15:26 PM"
	ISO_Date:  "2019-05-08"
	FM_Date:  "8/29/2019"
	UTC_Simplified:  "20190310T150000"
*/

Let([
//Sample entries for testing:
//	HrsOffset = -7;
//	source = "20190310T150000Z";

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

//Test if TS is in ISO 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"
)
];
NewTS
)

Very useful if a timestamp is not entered quite right, or you need to convert a date stamp to a timestamp, or you want to convert Unix or UTC timestamps to an FM Timestamp.

I developed this because a lot of APIs and other applications will use a variety of timestamps and this will convert them all to something that FileMaker can use.

Comments

		Mark Hodgins, Michigan State University Sep 1, 2022
Kyle - trying to run your function and it is failing on this section: 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) ); with the error "This parameter [CurrentTimeUTCMilliseconds] is an invalid Get function parameter. I have the TimeFormatAs function loaded as referred to in the header of the code. Am I missing something else?

		Kyle Williams, Augusto Digital Sep 1, 2022
Hi Mark, I have since updated my function to support millisecond timestamps but it seems I never updated it here in BrianDunning's site.. I have now updated it with a more recent version.

		Kyle Williams, Augusto Digital Sep 1, 2022
FYI, the Get(CurrentTimeUTCMilliseconds) function has been around since v13 so I'm not sure why you would be getting that message unless you are using v12 or earlier.. https://help.claris.com/en/pro-help/content/get-currenttimeutcmilliseconds.html

		Kyle Williams, Augusto Digital Sep 1, 2022
FYI, the Get(CurrentTimeUTCMilliseconds) function has been around since v13 so I'm not sure why you would be getting that message unless you are using v12 or earlier.. https://help.claris.com/en/pro-help/content/get-currenttimeutcmilliseconds.html

		Mark Hodgins, Michigan State University Sep 7, 2022
Thanks Kyle. Sorry to bother you. I was on an older version. Jumped onto newest version and was successful running the function. Appreciate the quick turn-around. My timestamps are now correct.

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

GetAsFMTimestamp ( source )

Comments

Support this website.

Support this website.