7 Replies Latest reply on Dec 10, 2016 3:07 PM by alquimby

    Time zone math


      I have seen many articles about time zone math but what I am doing is either the wrong AM or PM (like it is 25 hours and PM when I'd like 1 AM). Basically I do not mind manually changing the formula (as when Daylight Saving changes) and even if the hour is off by one (as in my target may or may not have have changed) it is close enough. If I know someone is ahead by X hours or behind by Y, one hour plus or minus is close enough. Sure, accuracy would be great, but if I my customer was ahead/behind by X, what formula would I use to calculate the current time of the customer? I am Eastern, and Daylight Saving is off. Ideas?

        • 1. Re: Time zone math
          David Moyer


          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

          2 Mountain

          3 Pacific

          ? Alaska - can't remember

          ? Hawaii - can't remember

          +2/3 Arizona - doesn't observe Daylight Time

          • 2. Re: Time zone math

            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.

            • 3. Re: Time zone math

              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. 

              • 4. Re: Time zone math
                David Moyer

                my example was meant as an overview, not how to code the solution.

                • 5. Re: Time zone math

                  General suggestions are all we can make as there are no specifics posted as to what was attempted and exactly how it failed.

                  • 6. Re: Time zone math

                    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.

                    • 7. Re: Time zone math

                      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.