Skip to main content

Setting current time in different timezones

  • July 9, 2018
  • 4 replies
  • 57 views
B

I’ve reviewed some posts, but I might just not understand how to do this.

I have multiple freelancers that live in different time zones around the world. Each freelancer has a record into my document with their contact info and their timezone. I want to set up a column that spits out their current time wherever they are. So in essence, something like this.

Bob Smith / London, UK / 4:00 PM
John James / New York, NY / 11:00 AM

I can input their Time Zone or their UTC time when I add them to the table. I just want to convert that information into an actual clock time, so I can know what time it is there without having to do the additional math.

Does anyone have a good tip for this?

    4 replies

    E
    Forum|alt.badge.img+17

    I think you could add a Time difference in hours, and then just do DATEADD(NOW() + {Time difference}, 'hours').

    E
    Forum|alt.badge.img+17

    It works:

    Even you could have a Time Zones table with the number, and link them to the Freelancers, so you have not to repeat the Time Diffs if you have so much collaborators.

      A
      Forum|alt.badge.img+17
      • Andy_Lin1
      • Inspiring
      • July 9, 2018

      @Elias_Gomez_Sainz Haha, I wish it were that simple; in certain places (such as Canada, where I am) they observe Daylight Saving Time, which will throw off the calculation.

      @Brett_Rakestraw
      If you just want the current time converted into your freelancers’ respective time zones, you can use this formula:
      DATETIME_FORMAT( SET_TIMEZONE(NOW(),{Time Zone}) ,'LT')
      Note that there isn’t a function to look up a city’s time zone, so you’ll have to enter that manually. The formula above assumes a field labeled “Time Zone”, and you should enter the proper time zone according to the list below (e.g. Europe/London and America/New_York):

      Note: the 'LT' format specifier only gives the 12-hour time, but won’t tell you if someone is a day ahead/behind. To include the date, you may wish to use 'lll' or 'llll' instead.

      If you want to extend the time zone conversion to other fields (e.g. embargos/deadlines/event start times), you may want to check out the following thread:

        B

        @Elias_Gomez_Sainz Haha, I wish it were that simple; in certain places (such as Canada, where I am) they observe Daylight Saving Time, which will throw off the calculation.

        @Brett_Rakestraw
        If you just want the current time converted into your freelancers’ respective time zones, you can use this formula:
        DATETIME_FORMAT( SET_TIMEZONE(NOW(),{Time Zone}) ,'LT')
        Note that there isn’t a function to look up a city’s time zone, so you’ll have to enter that manually. The formula above assumes a field labeled “Time Zone”, and you should enter the proper time zone according to the list below (e.g. Europe/London and America/New_York):

        Note: the 'LT' format specifier only gives the 12-hour time, but won’t tell you if someone is a day ahead/behind. To include the date, you may wish to use 'lll' or 'llll' instead.

        If you want to extend the time zone conversion to other fields (e.g. embargos/deadlines/event start times), you may want to check out the following thread:


        This worked perfect. Thanks!

        I also used this post to set up a Time Zone single select, so I don’t have to look up the time zones individually each time.

        Terms & ConditionsAccessibility statement