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

    Time zone math

    tlmurray

      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

          Hi,

          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
            philmodjunk

            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
              taylorsharpe

              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:

               

              2016-12-10T01:47:39+00:00

              2016-12-10T01:47:39Z

              20161210T014739Z

               

              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
                  philmodjunk

                  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
                    tlmurray

                    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
                      alquimby

                      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.