Solved

Calculating Time Zone Differences

Posted on 2008-06-22
11
2,241 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

914 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

12 Experts available now in Live!

Get 1:1 Help Now