time.Convert ( TmStamp ; localOffset ; FromForm ; toOffset ; ToForm )

Converts a timestamp from one format to another

Average rating: 4.3 (41 votes) Log in to vote

Rob Poelking - Show more from this author
Kiza Solutions
https://kizasolutions.com

Sample input:

time.Convert ( "07/20/1969 4:33:26 PM" ; +5 ; +7 ; "FM" ; "sql" )
time.Convert ( "1969-07-20 18:33:26" ; +5 ; +7 ; "sql" ; "unix" )
time.Convert ( "-14174794" ; +5 ; +7 ; "unix" ; "xml" )
time.Convert ( "1969-07-21T16:33:26" ; 0 ; 0 ; "xml" ; "fm" )
time.Convert ( "12/17/2020 11:04:21 AM" ; "" ; "FM" ; 2 ; "ISO" )

Sample output:

1967-06-23 16:33:26
-79687594
1967-06-23T16:33:26-00:00
6/23/1967 9:33:26 PM
2020-12-17T18:04:21+02:00

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

/**
 * ==================================================================
 * time.Convert ( TmStamp ; localOffset ; FromForm ; toOffset ; ToForm)
 * AUTHOR:     Rob Poelking (rob@ohiokajukenbo.com)
 * PARAMETERS:
 *            @TmStamp (string) Input timestamp as TEXT
 *            @localOffset (num)can be left blank - defaults to LOCAL offset
 *            @FromForm (enum) (FM,ISO,UNIX,SQL,XML)
 *            @toOffset (num)can be left blank or a positive or negative hour or fractional (5.5)
 *            @ToForm (enum) same as FromForm
 * 
 * VERSION:   2.1
 * RETURNS:
 *            (string) formatted timestamp
 *            
 * DEPENDENCIES:
 *            adjust for localized time format. See FMTSFormat and adjust if necessary. Defaults to US time format
 * PURPOSE:
 *            Converts a time stamp from one value to another
 *            also convert time zones
 * NOTES:
 *            none
 * REVISIONS:
 *            2023-01-10 - v2.2 thanks Dan Shockly for catching the -00:00 offset error
 *            2020-12-17 - v2.0 updated to fix ISO offset error and add time zone conversion
 *            2010-05-21 - Initial release v1.0
 * ==================================================================
 *
 */

Let([
/* THESE ARE JUST FOR TESTING */
/*
TmStamp = 1643805000 ; 
localOffset = -5 ; 
toOffset = 0 ;
Fromform = "unix" ; 
ToForm = "fm" ; 
*/
	
	/* NOTE: DATE IS BASED ON USA STANDARD FORMAT! Change this if your standard format is different */
	FMTSformat = "mm/dd/yyyy hh:nn:ss" ; // eg "dd/mm/yyyy hh:nn:ss"

	fromOffset = If ( IsEmpty (localOffset)  ; Round ( (GetAsNumber ( Get(CurrentTimestamp) * 1000 ) - Get(CurrentTimeUTCMilliseconds))/3600000 ; 0 ) ; localOffset ) ;

	offset = toOffset ;
	offsetDelta = Abs(fromOffset - toOffset) ;
	offsetDirection = choose ( fromOffset < toOffset ; -1 ; 1 ) ;
	ts = TmStamp ;
	fs = offsetDelta * offsetDirection ;
	Fr = Upper ( FromForm ) ; 
	To = Upper ( ToForm ) ; 
	
	//	Validate inputs
		FormList = List (
				"fm";
				"filemaker";
				"unix";
				"iso";
				"sql";
				"xml");
		FrValid = ValueCount ( FilterValues ( Fr ; FormList ) ) ;
		ToValid = ValueCount ( FilterValues ( To ; FormList ) ) ; 
		MilTZa = "ABCDEFGHIKLM";
		MilTZb = "NOPQRSTUVWXY" ;
		MilTZc = "Z" ; 
		offsetH = Truncate ( offset ;0 ) ;
		offsetM = Mod ( offset ; 1 )*60 ;
		sgn = Choose ( Sign ( offset ) >= 0 ; "-" ; "+" );
		fsDisplay = sgn & Right ( "00" & Abs(offsetH) ; 2 ) &":"& Right ( "00" & Abs(OFFSETM) ; 2 ) ;
		fs = Case (
				IsEmpty ( fs )  ; "" ; 
				ValueCount( Filter (fs ; MilTZa ) ) ; -1 * GetAsNumber ( Position( MilTZa; Upper (fs); 1; 1 )) * 3600 ;
				ValueCount( Filter (fs ; MilTZb ) ) ; +1 * GetAsNumber ( Position( MilTZb; Upper (fs); 1; 1 )) * 3600 ;
				ValueCount( Filter (fs ; MilTZc ) ) ; 0 ;
				PatternCount( fs; ":" )	; GetAsNumber ( GetAsTime ( Substitute ( fs ; "+" ; "" ))) / 3600 /*ss in one hour*/ ; 
				GetAsNumber ( fs * 3600 )
				) ;



		fromFM = ValueCount( FilterValues( "FM¶FILEMAKER"; fr ) ) ; 
		fromUnix = ValueCount( FilterValues( "UNIX"; fr ) ) ; 
		fromSQL = ValueCount( FilterValues( "SQL"; fr ) ) ; 
		fromISO = ValueCount( FilterValues( "ISO"; fr ) ) ; 
		fromXML = ValueCount( FilterValues( "XML"; fr ) ) ; 

		toFM = ValueCount( FilterValues( "FM¶FILEMAKER"; to ) ) ; 
		toUnix = ValueCount( FilterValues( "UNIX"; to ) ) ; 
		toSQL = ValueCount( FilterValues( "SQL"; to ) ) ; 
		toISO = ValueCount( FilterValues( "ISO"; to ) ) ; 
		toXML = ValueCount( FilterValues( "XML"; to ) ) ; 

	//Format strings	
		FMTSFormat = FMTSformat ;
		NIXTSFormat = 0 ; //N/A Unix is a signed number value that represents dates from 1901-12-13 to 2038-1-19 03:14:07UTC
		SQLTSFormat = "yyyy-mm-dd hh:nn:ss";
		ISOTSFormat = "yyyy-mm-ddThh:nn:ss[~offset]" ; 
		XMLTSFormat = "yyyy-mm-ddThh:nn:ss[~offset]" ; //optionally can have an offset

		Valid = frValid and toValid ; 

		fromTS = 
			Case (
				fromFM ; GetAsTimestamp ( ts ) ; 

				fromUnix ; 
					Let ([
						ts = GetAsNumber ( ts ) ; 
						epoch = GetAsNumber ( GetAsTimestamp ( "1/1/1970 0:00:00" ) ) ; //Unix standard epoch = 0
						sgn = Sign ( ts ) ; 
						fs = sgn * GetAsNumber (fs) 
						] ; GetAsTimestamp ( epoch + ts )) ; 

				fromSQL ; 
					Let ([
						yyyy = Left ( ts ; 4 ) ; 
						mm = Middle (ts ; 6 ; 2 ) ; 
						dd = Middle ( ts ; 9 ;2 ) ; 
						hh = Middle ( ts ; 12 ; 2 );
						nn = Middle ( ts ; 15 ; 2 ) ; 
						ss = Middle ( ts ; 18 ; 2 )
						];GetAsTimestamp (  
								(Substitute ( FMTSFormat ;
								["yyyy" ; yyyy ];
								["mm" ; mm];
								["dd" ; dd];
								["hh";hh];
								["nn";nn];
								["ss";ss]
								)))) ; 

				fromISO ; 
					Let ([
						yyyy = Left ( ts ; 4 ) ; 
						mm = Middle (ts ; 6 ; 2 ) ; 
						dd = Middle ( ts ; 9 ;2 ) ; 
						hh = Middle ( ts ; 12 ; 2 );
						nn = Middle ( ts ; 15 ; 2 ) ; 
						ss = Middle ( ts ; 18 ; 2 )
						];GetAsTimestamp (  
								(Substitute ( FMTSFormat ;
								["yyyy" ; yyyy ];
								["mm" ; mm];
								["dd" ; dd];
								["hh";hh];
								["nn";nn];
								["ss";ss]
								))));
				fromXML ; 
					Let ([
						yyyy = Left ( ts ; 4 ) ; 
						mm = Middle (ts ; 6 ; 2 ) ; 
						dd = Middle ( ts ; 9 ;2 ) ; 
						hh = Middle ( ts ; 12 ; 2 );
						nn = Middle ( ts ; 15 ; 2 ) ; 
						ss = Middle ( ts ; 18 ; 2 );
						fs1 =  GetAsTime ( Right ( ts ; Length ( ts) - 19 )) 
						];GetAsTimestamp (  
								(Substitute ( FMTSFormat ;
								["yyyy" ; yyyy ];
								["mm" ; mm];
								["dd" ; dd];
								["hh";hh];
								["nn";nn];
								["ss";ss]
								))))
				) ; 
		toTS = Case (
			toFM ; fromTS + fs; 
			
			toUnix ; 
				Let ([
					ts = fromTS + fs ; 
					epoch = GetAsNumber ( GetAsTimestamp ( "1/1/1970 0:00:00" ) ) ; //Unix standard epoch = 0
					sgn = Sign ( ts ) ; 
					fs = sgn * GetAsNumber (fs) 
					] ; GetAsNumber (( ts - epoch + fs ))) ;
					
			toSQL ; 
				Let ([
					ts = fromTS + fs ; 
					yyyy = Year ( ts ) ; 
					mm = Right ( "00" & Month ( ts) ; 2 ) ; 
					dd = Right ( "00" & Day (ts) ; 2) ; 
					hh = Right ( "00" & ( Hour (ts) ) ; 2 );
					nn = Right ( "00" & Minute (ts) ; 2 ) ; 
					ss = Right ( "00" & Seconds ( ts) ; 2)
					] ;

					Substitute ( SQLTSFormat ;
						["yyyy" ; yyyy ];
						["mm" ; mm];
						["dd" ; dd];
						["hh";hh];
						["nn";nn];
						["ss";ss]
						)
				);
			
			toISO ;  
				Let ([
					ts = fromTS + fs ; 
					yyyy = Year ( ts ) ; 
					mm = Right ( "00" & Month (ts) ; 2 ) ; 
					dd = Right ( "00" & Day (ts) ; 2) ; 
					hh = Right ( "00" & Hour (ts) ; 2 );
					nn = Right ( "00" & Minute (ts) ; 2 ) ; 
					ss = Right ( "00" & Seconds (ts) ; 2) 

					] ;

					Substitute ( ISOTSFormat ;
						["yyyy" ; yyyy ];
						["mm" ; mm];
						["dd" ; dd];
						["hh";hh];
						["nn";nn];
						["ss";ss];
						["[~offset]";   fsdisplay ]

						)
				);

			toXML ;  
				Let ([
					ts = fromTS + fs ; 
					yyyy = Year ( ts ) ; 
					mm = Right ( "00" & Month (ts) ; 2 ) ; 
					dd = Right ( "00" & Day (ts) ; 2) ; 
					hh = Right ( "00" & Hour (ts) ; 2 );
					nn = Right ( "00" & Minute (ts) ; 2 ) ; 
					ss = Right ( "00" & Seconds (ts) ; 2) ;
					sgn = Sign (fs) ; 
					sg = If ( sgn >= 0 ; "+" ; "-");
					fs = If ( IsEmpty ( FS ) ; "" ; 
						Let ([
							oh = Abs (Int (fs)) / 3600;
							ohh = Right ( "00" & oh ; 2 ) ;
							on = (Abs(fs)/3600 - oh) * 60 ; 
							onn = Right ( "00" & on ; 2 ) 
							]; sg & ohh & ":" & onn) )
					] ;

					Substitute ( XMLTSFormat ;
						["yyyy" ; yyyy ];
						["mm" ; mm];
						["dd" ; dd];
						["hh";hh];
						["nn";nn];
						["ss";ss];
						["[~offset]"; fs]
						)
				)
				
			)
		
		] ;

		tots

		)
/* Notes: Rob Poelking */

Converts the time stamp formats of FileMaker, XML (ISO 8601), SQL or Unix into another format by specifying the FromForm and ToForm. You can convert a time stamp in unix to xml or FileMaker to SQL.
UPDATED: now converts to specified time zone.

Comments

		Chris McCue, Dallas, TX Sep 3, 2013
Great function! Moving records back and forth between FileMaker SOAP web services and MySQL tables is made much simpler with this handy calculation.

		Christos Savva, Cyprus Jul 13, 2017
There is an issue with XML format for Eastern Hemisphere times. This line: sg = If ( sgn > 1 ; "+" ; "-"); Should be sg = If ( sgn = 1 ; "+" ; "-"); Otherwise you never get +ve timezones.

		Jason Wood, Define Database Mar 19, 2019
The example time.Convert ( "1967-06-23T16:33:26-05:00"; "" ; "xml" ; "fm" ) Returns a "?" for me in FileMaker 17...

		Rob Poelking, Kiza Solutions Mar 20, 2019
Thank you, Jason. I had made a recent change to the function and I am using it in FM 17. I just input that same string and got back the correct form: 6/23/1967 9:33:26 PM. Note that (at least for xml) it is correctly evaluating the time offset of 5 hours.

		Jason Wood, Define Database Mar 21, 2019
Hi Rob, I believe it's because of my Canadian localization, where dates are YYYY-MM-DD. I found another solution so no worries about fixing for me but I do think it's likely there is a dependency in here relating to the system format.

		Jason Wood, Define Database Mar 21, 2019
Found it... if you are outside USA, you would have to update this line: FMTSFormat = "mm/dd/yyyy hh:nn:ss" ;

		caitlin Sep 24, 2020
Jason Woods - thanks for the heads up - I'm using this, and would have driven myself crazy if I didn't read your comment!

		John Garbe Dec 15, 2020
Thanks for sharing the great function. The offset doesn't work as I would expect, as these both generate the same result (12/15/2020 6:15 PM) timestamp.Convert ( "2020-12-15T18:15:00.000Z" ; 0 ; "ISO" ; "FM" ) timestamp.Convert ( "2020-12-15T18:15:00.000Z" ; -6 ; "ISO" ; "FM" )

		Rob Poelking, Kiza Solutions Dec 17, 2020
Thank you, John, I've updated it to correct and have added the additional functionality to convert it completely from one timezone to the next.

		Gustavo, Transcontinental Inc. Oct 8, 2021
Hi Rob, I am just trying to create the Custom Function in my FM db, but I am getting an error, telling me that the localOffset parameter is not been found, I checked, I believe it is because is been declared as empty, Should I move something else to making it work ?

		Gustavo, Transcontinental Inc. Oct 8, 2021
I got it, I was needed to add all 5 parameters creating the Custom Functions, I am sorry about that..

		Dan Shockley Jan 10, 2023
First, thank you to Rob for putting together this useful function. I found a (normally) minor issue: If the offset is 0 (local IS already UTC) and you are converting to ISO, the offset portion of the output is: "-00:00", which is a problem, since the ISO 8601 standard says that a zero-offset should be "+00:00". See https://en.wikipedia.org/wiki/ISO_8601#Time_offsets_from_UTC I've run into a case already where that non-standard negative symbol for zero-offset results in an error: calling MailChimp API with a query filtering by "since_last_changed", which takes an ISO 8601 timestamp. It was failing with an Internal Server Error until I changed to +00:00. The code change that fixes this is in line 66, which should be: >>> sgn = If ( Sign ( offset ) >= 0 ; "+" ; "-" ); â€¦instead of: >>> sgn = Choose ( Sign ( offset ) > 0 ; "-" ; "+" );

		Rob Poelking, Kiza Solutions Jan 11, 2023
Thank you Dan. Added that corrrection

		Dan Shockley Jan 30, 2023
Great! Rob, can you clarify something for me? I'm getting a strange result, but perhaps I'm missing something. If I use the function as follows: time.Convert ( "2022-09-07T16:25:49+00:00" ; -5 ; "ISO" ; 0 ; "FM" ) /* 9/7/22, 4:25 PM, UTC */ â€¦I expect to get back a FileMaker timestamp for that date at 11:25 AM. Instead, it seems to move _forward_ by 5 hours to 9:25 PM. I think the issue is that the offsetDelta variable is taking the Absolute value of the difference (and then the "fs" variable is set to that offsetDelta and used from then on to calculate the result). I don't think that ABS() function is correct. What do you think? Is there something else it is accounting for?

		Rob Poelking, Kiza Solutions Jan 30, 2023
I think I had 2 errors there, Dan. I didn't need the ABS and I also have the wrong sign.

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

time.Convert ( TmStamp ; localOffset ; FromForm ; toOffset ; ToForm )

Comments

Support this website.

Support this website.