Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Calculating Time Zone Differences

Posted on 2008-06-22
Medium Priority
Last Modified: 2008-06-26
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".
Question by:trishahdee
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2

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

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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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.

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).


Expert Comment

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

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!
LVL 19

Accepted Solution

billmercer earned 2000 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.
 North Dakota
 South Dakota


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...

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!!
LVL 19

Expert Comment

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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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 course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question