I used to have a Boolean field flag_DaylightTime and another unstored calculation that showed the time in the zone that the customer was in, theoretically that is (I used area code, as opposed to zip code). Anyhoo, my area code table held my custom time zone offset. Here's a rough semblance of what my memory can come up with. This is EST/EDT plus an offset ...
-1 East of the East Coast (e.g. VI, I think)
0 Eastern Time
+1 Central Time
? Alaska - can't remember
? Hawaii - can't remember
+2/3 Arizona - doesn't observe Daylight Time
Time and time stamp fields store their values as an integer in seconds so subtracting or adding an hour requires subtracting or adding 3600 seconds.
Throughout the world there are all kinds of exceptions as to who is on daylight savings and not and the time zone lines do not follow Longitude linens like they technically should. That is why may data sources that have to deal with time from around the world work with Coordinated Universal Time (kind of a successor to Greenwich Mean Time) and you'll see a lot of computer times stored that way and just converted on the fly to local time for end users.
Some common UTC formatting are:
But then you have to figure out what to display to end users. This is a common issue that calendaring databases have to solve.
my example was meant as an overview, not how to code the solution.
General suggestions are all we can make as there are no specifics posted as to what was attempted and exactly how it failed.
I left something out: I have a field for the offset, as in plus 2 or minus 4. There are not that many records so it is a quick lookup on the web. Also, I am the only user.
The attached has 2 tables: TimeZone with 1 record for each Continental USA time zone (you can always add more) with the number of seconds in an hour to be added or subtracted (assumes you are in Central Time Zone). Main file adjusts Customer Time by adding the seconds (will add a negative number for MST and PST) to the My Time field based on Customer Time Zone.
TimeZones.fmp12.zip 68.8 K