Experiences of a FileMaker Pro Database Developer

Unix Timestamp Custom Function

I needed to create a custom function to convert a standard FileMaker timestamp to the Epic Unix Timestamp. We are beginning to develop in Redshift on AWS and need to pass data from FileMaker to a table in Redshift.

I created the custom function to automatically adjust to daylight savings time here in the US. The function would need to be adjusted for use outside the US. You will also need Howard Schlossberg’s isDayLightTime custom function.

UnixtimeStamp ( _date; _time; _UTC_offset )
//UnixtimeStamp ( _date; _time; _UTC_offset )
//Written by Mathew Greger, matt@mattgreger.com
//Converts FileMaker Date and Time to Unix Timestamp
//Automatically adjusts for Daylight Savings Time (US)
//Requires isDaylightTime custom function by Howard Schlossberg

//_date = standard FileMaker Date
//_time = standard FileMaker Time
//_UTC_offset = Current Timezone Offset from UTC

Let ([

//daylight savings time = 1, otherwise 0
_dst = isDaylightTime ( _date);

//convert time to GMT, must pass UTC offset
_gmt = _time + ((5 - _dst) * 3600)


//convert timestamp to number
GetAsNumber ( 
//must use GMT for time
Timestamp ( _date;  _gmt ) )
//convert number to Unix Timestamp (difference in second from 1/1/1970 to 1/1/0001)
- 62135596800


Post to Twitter