Convert Unix time stamp to FMPro time stamp
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.