Solved

# Calculating Time Zone Differences

Posted on 2008-06-22
2,248 Views
I haven't worked with FM for some time and I can't find this answer in the books/manuals I have....

I have a lookup field "Time Zone" that finds and displays the time zone name ("Eastern", "Central", etc.) based on area code.  It works fine.

What I now need is a way to use "Time Zone"  to calculate what the current  time is in, say, "Pacific" if I'm in "Eastern" and show Pacific local time in a field called "Time Zone Calc".  IOW, if the current time is 5:29pm EST then "Time Zone Calc" should say "2:29pm".
0
Question by:trishahdee
• 3
• 3
• 2
• +2

LVL 1

Expert Comment

ID: 21842352
0

LVL 9

Expert Comment

ID: 21842758
You will have to establish your time zone relative to GMT (Grenich mean time) then use get(current time) to get your local time. Convert it to GMT. Then based on area code or time zone recompute the time in that zone. For example PST is GMT-8 hours and Eastern is GMT-5 hours
0

LVL 1

Expert Comment

ID: 21842911
I do have a zipcode db and every zip code has a field where I store the time difference to my zip.
E.g. In zip 92506 it's 5:40 pm then in 10003 which is NY zipcode I store a +3, so where ever I need to process the time all I do is add +3  to 5:40pm and it tells me the NY time.

The reason to do theis by zip is that there are certain areas in the US that have different time zones like AZ.
0

LVL 19

Expert Comment

ID: 21846860
Jvaldes' suggestion is the correct way to handle this. Convert the local time to GMT, then convert to the remote time from that. It's the most flexible and error-resistant method.

A quick-and-dirty alternative is to just add another field to your time zone lookup table and store the local time difference in that field. Then use a relationship based on the time zone name or ID, something like this:

calculated field Remote_Time
= Local_Time - Areacodedata::TimeZoneDiff

But don't do this, it's a bad idea. Go with Jvaldes' suggestion.
0

LVL 1

Expert Comment

ID: 21847111
Ok, and how do you know what is the time for X place unless you store the place and the time zone?
If you don't store it, then you'll need to enter it every time (enter place and GMT difference).

0

LVL 9

Expert Comment

ID: 21847291
Phaeberle, that is the intention. Trishadee has a database of locations with time zones.
0

Author Comment

ID: 21848837
So I'm stumped on how to implement jvaldes soloution.  I don't have a get(current time) function.  I'm using FM7 and that may be a function of a newer version.

Also, to get around the "split area code" problem, I just downloaded a csv file with "ZIP","CITY","STATE","COUNTY","LATITUDE","LONGITUDE","AREACODE" that I could use to make things more precise but the lat/long is a whole other problem.

Where do I go from here?

BTW, thanks for working with me!
0

LVL 19

Accepted Solution

billmercer earned 500 total points
ID: 21850592
FM 7 does have the Get(CurrentTime) function. There is no space, maybe that's why you're having trouble?

In my opinion, the best option is to provide a time zone field associated with each individual contact, and populate this with a default value based on either the zip code or the state. You can then go in and manually correct any exceptions.

Here's how a calculated field would work. Create a field with each contact called LocalTimeOffset, and populate it with the difference between your local time and that of the area you're calling. So if Bob is 3 hours earlier than you, you would put -3 in that field. Then you have a calculated field that adds the time difference times 3600 (the number of seconds in an hour) to your local time. It would look something like this:

RemoteTime = Get(CurrentTime) + LocalTimeOffset * 3600

When you download a zip code database, the latitude and longitude information is an approximation of the center of the zip code region, and isn't really relevant to area codes. You should not need to use latitude/longitude info for this particular function.

The problem is that you're dealing with three different ways of dividing up the country into zones. Area codes are based on telephone company networks phone company networks and equipment. Zip codes are based on the truck routes taken by postal workers delivering the mail. And time zones are based on a combination of political and geographical boundaries.

It's not always practical to assign a single time zone to an area code, because they can overlap. For example, the 850 area code is for northwest Florida, including the panhandle. But half of this area code is in central time and the other half is eastern time. Zip codes are more popular for this purpose because they're much smaller areas, so any discrepancies will be a lot smaller.

If you don't want to deal with the complexity of zip codes, another quick and dirty approach would be to base your time zones on the state. There are 13 states that have more than one time zone, and most of these have one predominant time zone for most of the state, with a much smaller part of the state in another time zone. Here's a list of the split states.
Florida
Idaho
Indiana
Kansas
Tennessee
Kentucky
Oregon
North Dakota
South Dakota
Texas
Michigan

0

Author Comment

ID: 21861869
Just wanted you to know that I am still working on your suggestions and haven't got to a point where I can update you yet.  I'll let you know when I know if it worked...
0

Author Comment

ID: 21870763
billmercer, it worked!

You were right about everything.  I decided that I wanted to get as accurate as possible.  So I bought a reasonably priced database (\$25) that included "ZIP Code", "State", "City", "County", "Area Code", "Time Zone", and "Daylight Savings".

Then I followed your instructions using the Time Zone instead of the Area Code which took care of the split-state and split-area code problem.

Thank you very much for working with me!!
0

LVL 19

Expert Comment

ID: 21874335
Glad to hear you were able to resolve it!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes thâ€¦
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" Anâ€¦
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to â€¦
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threatâ€¦