Convert filemaker 6 date/time to Unix timestamp

[Batfastad]

Hi everyone

I've not been on here for a while... used to be a regular in the FileMaker 4-6 days.

Apologies if this is not the correct section to post in, this question relates to FileMaker 5/6 calculation fields.

Anyway, the time is coming to start porting a few of our simple databases over to MySQL.

At the moment we have a hybrid solution running over our intranet using PHP and FX.php to access filemaker data, and MySQL for some of the other databases.

There are a few databases in our solution that simply store vast numbers of records, and loading them through FX.php is not very efficient so I was going to get those simple databases copied over.

My particular problem with doing this is the date/times of record creation.

When I export the data, I have a PHP script which basically loops through the records output using FX.php, and prints them as an HTML page in the following format:

INSERT INTO `notes` VALUES('','$company_id',$time_now,'$user_name',$material,$notes,$mag,$type,$where_who);

So then I just copy and paste all those SQL insert commands into phpMyAdmin

Another upside of outputting as HTML rather than text is that FireFox / IE automatically converts all the entities created by FileMaker's XML export into their proper characters for storing in MySQL.

This worked well for some other databases I did a while back, but I've spotted a problem with my handling of dates/times.

I have a filemaker calculation field which is supposed to take my filemaker record creation date and creation time fields and convert them into a UNIX timestamp.

But I've noticed a problem with this calculation as it doesn't return correct values when BST (GMT +1) is operation... it gives the times an hour forward.

So I decided to convert to a timestamp using PHP and the mktime function:

http://uk2.php.net/manual/en/function.mktime.php

Should be easy!

But to do it this way I need a filemaker calculation field function that can tell me if a certain date/time is in GMT or BST.

Then I can pass either a 0 or a 1 to the is_dst parameter of the PHP mktime() function

Leaving it up to PHP to work out automatically whether the date is GMT or BST can produce very unpredictable results.

Does anyone know of a way within a FileMaker calculation field to detect if a particular date is GMT or observing Daylight Savings Time?

All the date/times in our databases are between 1999 and 2007

The only way I can think of doing this is setting up a database containing all of these dates

http://en.wikipedia.org/wiki/British_summer_time#Start_and_end_dates_of_British_and_Irish_Summer_Time

... and comparing the value to see whether it's in any of those ranges.

Just wondered if anyone knew a better way?

Thanks

Ben

[Batfastad]

Ok I've actually solved this without filemaker and found a great PHP function to do the hard work here...

http://www.devscripts.net/browse/116.php

Hope this helps someone else out