Solved

Calculating Time Zone Differences

Posted on 2008-06-22
11
2,226 Views
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".
0
Comment
Question by:trishahdee
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 1

Expert Comment

by:masterhacker
ID: 21842352
0
 
LVL 9

Expert Comment

by:jvaldes
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

by:phaeberle
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

by:billmercer
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

by:phaeberle
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Expert Comment

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

Author Comment

by:trishahdee
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

by:
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.
 Alaska
 Florida
 Idaho
 Indiana
 Kansas
 Tennessee
 Kentucky
 Oregon
 North Dakota
 South Dakota
 Nebraska
 Texas
 Michigan

0
 

Author Comment

by:trishahdee
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

by:trishahdee
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

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now