1 Reply Latest reply on Mar 23, 2013 2:28 AM by GeoffreyMartin

    Converting UNIX times with Daylight Savings Times

    GeoffreyMartin

      Title

      Converting UNIX times with Daylight Savings Times

      Post

           I recently discovered that I had a problem with my conversion of my unix date_time to FM timestamps, that didn't account for DST.  I found an article on the knowlegebase but it was outdated, the DST dates have changed. I've updated that formula:

            

           If (LCATS::date < (Date(3 ; 7  ; Year(LCATS::date)) + 
           Middle("0654321" ; DayOfWeek(Date(3 ; 7 ; Year(LCATS::date))) ; 1)) 
           or LCATS::date >= (Date(11 ; 1 ; Year(LCATS::date)) + Middle("0654321" ; DayOfWeek(Date(11 ; 1 ; Year(LCATS::date))); 1)) ; -8 ; -7)
            
           The -8 and -7 are for my timezone, to use change those numbers to your local timezone.  
            
           I use the above calculation for the offset parameter for the custom formula at the bottom to convert the unix times.  There's only one thing that isn't taken into account here...the timechange happens at 0200.  I have another field that returns hour of day (calc_hod), I tried using the above like this: 
            
                     If ((calc_hod  ≥ 2 and LCATS::date < 
                     (Date(3 ; 7  ; Year(LCATS::date)) + 
                     Middle("0654321" ; DayOfWeek(Date(3 ; 7 ; Year(LCATS::date))) ; 1))) 
                     or (calc_hod  ≥  2 and LCATS::date >= (Date(11 ; 1 ; Year(LCATS::date)) + Middle("0654321" ; DayOfWeek(Date(11 ; 1 ; Year(LCATS::date))); 1))) ; -8 ; -7)
                      
                     But now, it's changing every day.  Order of operations??  My brain is mush.  Any help?
                      
           I used the time.convert formula (a brilliant formula) created by Rob Poelking at http://www.briandunning.com/cf/1161 to do the unix conversions.
            
           Thanks in advance.
            
            

        • 1. Re: Converting UNIX times with Daylight Savings Times
          GeoffreyMartin

               I figured it out and here's what I've got to share.  For me I have a bunch of date_time timestamps from a SQL server that need to be converted on each record.  So I created a DST field to calculate whether or not it's Daylight Savings Time and to supply the correct offset for the conversion.

               So here's the DST calculation (as in the previous post, I'm using the custom time.convert function at the link listed there):

                

               DST (Calculation ; Number)
                
               Let ( 
               [timeeval =  time.convert (YourUnixDateTime ; "0" ; "Unix" ; "FM") ] ;
               //Converts Unix timestamp to FM timestamp in GMT.
               //This was done to avoid a circular arguement when the conversion on this particular
               //UnixDateTime field to my local timezone would happen in a different calculation.
                
               If (
                        timeeval < ( Timestamp ( Date (3 ; 7  ; Year( timeeval )) + 
                         Middle ( "0654321" ; DayOfWeek ( Date (3 ; 7 ; Year ( timeeval ))) ; 1) ;
                         Time (10 ; 0 ; 0)))
                   or  timeeval  ≥  ( Timestamp ( Date (11 ; 1 ; Year(timeeval)) + 
                         Middle ( "0654321" ; DayOfWeek ( Date(11 ; 1 ; Year( timeeval ))); 1) ;
                         Time (9 ; 0 ; 0))) ;
                
                -8 ; -7)
               //If you are not in PST you'll need to adjust these numbers to your timezone,
               //Keep the ratio between the colored pairs.
               )
                
               Now that you have that done, the Converstion calculations look like this:
                
               MyTimestamp (Calculation ; timestamp)
                
               Case (Unix_date_time=0 ; "" ; time.convert (Unix_date_time ; DST ; "Unix" ; "FM"))
               // The first arguement is to keep a field from showing a 1/1/1970 00:00:00 when the field
               // being converted is empty.
                
               Please let me know if I've got something wrong.  It's working great for me and I won't have to revisit this as it will continue to calculate these time changes in the future.