Setting current time in different timezones


#1

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?


#2

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


#3

It works:

27

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.


#4

@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:


#5

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.