AnsweredAssumed Answered

Convert Unix time stamp to FMPro time stamp

Question asked by captainboom on Jan 23, 2010
Latest reply on Aug 20, 2015 by TerryMorse

Title

Convert Unix time stamp to FMPro time stamp

Post

I found a solution to a problem I'm working on, and didn't find anything like it in the forums, so thought I would post my solution here.

 

I am using FM to connect to a remote MySQL database that is used for my Miva Merchant ecommerce software. When an order is placed, a time stamp is placed on the order in a field named 'orderdate' in the database. That time stamp exists as a number, for example 1262297759, which corresponds to 12/31/2009 15:15:59 Mountain Daylight Time on my particular remote server. When I use the FM functions to convert into a human readable form, I get a time stamp of 12/31/0040 10:59:59 PM, due to the difference in the way that Unix and FM keep track of time. Unix uses the time from 1970-01-01 00:00:00 as the zero point.

 

The way I solved my problem is to create a new calculation field in the FM database, call it 'date_time' for example, and make it equal the following function:

 

date_time = GetAsTimestamp (GetAsNumber (orderdate)) + GetAsNumber (Timestamp ("01/01/1970" ; "00:00:00") - 25200)

 

This function grabs the number from the 'orderdate' text field, adds the number which is the difference between FM time and Unix time, and subtracts the number of seconds in 7 hours, as that's the difference between UTC and Mountain Daylight Time.

 

Hope this helps anyone else who might need it.

Outcomes