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

Usability: A Lesson From Evernote’s Penultimate 6 Blunder

Penultimate 5

As software/app developers it's critical that we understand usability. We must learn who our users are and their behavior. Essentially, we build use cases and user personas. It’s our job to figure out what is working and what doesn’t work in their … [Continue reading]

It’s Been a While

It's been a while since I posted to this blog. No excuses, just adjusting to working full time. There has been a lot going on with my FileMaker development and I even heard from Commander "Devil Doc" Baker who's now stationed at Camp Lejeune. It was … [Continue reading]

FileMaker 12 Review – Part 1

We've been running the FileMaker 12 family of products since August 2012. These are my impressions and experiences in a real world, live development environment. My experience is taking an existing FileMaker 11 solution and converting it to run in … [Continue reading]

Creating QR Codes with FileMaker and KAYWA API

I want to thank Kevin Murphy for submitting his FileMaker QR Code solution using the KAYWA API for creating QR Codes. With the fate of Google's API for creating QR Codes unknown, it's good to see alternative solutions. Kevin's recent submission has … [Continue reading]

Recent Changes

I've recently made several changes and have been absent for a while in both the FileMaker community as well as my blog. I hope to be on track in the next few weeks as I dive into some new tools for optimization and new areas with FileMaker products. … [Continue reading]

A Time for Thanks

It's that time of year to celebrate Thanksgiving, to be thankful for the many blessing that we all have. This year, I have the pleasure of working with a military psychiatrist, CDR Alfredo “Devil Doc” Baker, MD, MC, USN, who is stationed in … [Continue reading]

Helping Our Soldiers Quit Smoking, Part 3


Planning the Database The way I see the Tobacco Cessation database laying out is that there will essentially be two tables. One table stores the Marine & Sailor's basic information. This is the people table. In this table is everything that … [Continue reading]

FileMaker Server, Please Abort Cancelled Finds!

I'm usually not one that likes to rant or get on a soapbox. I find this unproductive and I'm not really sure it does much to contribute, plus I feel that most readers don't like a complainer. However, there is one topic that I'm in a uproar … [Continue reading]

Helping Our Soldiers Quit Smoking, Part 2

Recommendations for Resources: Where to start with FileMaker Pro This is part 2 of a case study that will follow the development of a FileMaker database by a military psychiatrist to assist with his smoking cessation program for his Marines & … [Continue reading]