AnsweredAssumed Answered

Coverting EST Timestamps to GMT Timestamps

Question asked by xxx202xxx on Aug 31, 2017
Latest reply on Jan 4, 2018 by fmpdude

I tell ya the more I work with Filemaker the deeper I seem to go with issues that I run into.  I guess it's one way to learn.  Here we go.  I have never dealt with functions before and after reading from the discussion board, "Converting Unix Times with Daylight saving Times" I followed that to Brian Dunning.com/cf/1161 - Unix Conversions.  I pasted the code from his website in case someone run across my post.  I just started learning about ExecuteSql.  Now I must learn about functions.  With that being said would I simply past this into the function view?  How would I get this bad boy to work in my system?  I am using Filemaker Timestamps and I do see in the //Validate input form list - filemaker so I will assume that the timestamp I am using can be converted to GMT.  I do know that GMT is 4 hrs ahead during daylight savings and 5 hrs ahead not in daylight savings or maybe I have that wrong.  Please help.

 

//time.Convert ( TmStamp ; offset ; FromForm ; ToForm)
//Author: Rob Poelking rob@ohiokajukenbo.com
//Version 1.0
//2010-05-21
/*
Allowable form keys: FM, FileMaker, Unix, SQL, XML
Offset entered as a signed number or time, or military time zone (valid exampls:  -5, +3:00, Z)
Fractional seconds are rounded
*/

Let([
ts = TmStamp ;
fs = offset ;
Fr = FromForm ;
To = ToForm ;

// Validate inputs
  FormList = List (
    "fm";
    "filemaker";
    "unix";
    "sql";
    "xml");
  FrValid = ValueCount ( FilterValues ( Fr ; FormList ) ) ;
  ToValid = ValueCount ( FilterValues ( To ; FormList ) ) ;
  MilTZa = "ABCDEFGHIKLM";
  MilTZb = "NOPQRSTUVWXY" ;
  MilTZc = "Z" ;
  fs = Case (
    IsEmpty ( fs )  ; 0 ;
    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 ) ) ;
  fromXML = ValueCount( FilterValues( "XML"; fr ) ) ;

  toFM = ValueCount( FilterValues( "FM¶FileMaker"; to ) ) ;
  toUnix = ValueCount( FilterValues( "unix"; to ) ) ;
  toSQL = ValueCount( FilterValues( "SQL"; to ) ) ;
  toXML = ValueCount( FilterValues( "XML"; to ) ) ;

//Format strings
  FMTSFormat = "mm/dd/yyyy hh:nn:ss" ;
  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";
  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 + fs )) ;

    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]
        )))) ;

    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]
        )  ) )- fs1)
    ) ;
  toTS = Case (
   toFM ; fromTS ;
  
   toUnix ;
    Let ([
     ts = fromTS ;
     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 ;
     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]
      )
    );
  
   toXML ; 
    Let ([
     ts = fromTS ;
     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 = 1 ; "+" ; "-");
     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 */

Outcomes