I recently had to build a table to manage a number of events that happen outside of my time zone, and I thought I’d share what I’d learned in case someone else needs to do the same.
Now, Airtable offers quite a few functions and features to handle timestamps:
Today I’m mainly going to focus on SET_TIMEZONE.
How SET_TIMEZONE behaves
Here’s a sample table I’m going to draw examples from:
{Given Time} corresponds to [date] below
{Time Zone} corresponds to [tz_identifier] below
SET_TIMEZONE takes two arguments, [date] and [tz_identifier], with the description “Sets a specific timezone for a datetime.” I thought this meant that it would apply the [tz_identifier] to the [date], i.e.:
[date] = July 1, 2017 00:00 (unmarked)
[tz_identifier] = America/Toronto
[output] = July 1, 2017 00:00 EST
However, what it actually appears to do is convert the given [date] to the time zone given by [tz_identifier]. This is easily tested by toggling the “Use the same time zone (GMT) for all collaborators” option in the [date] field. If it were applying the [tz_identifier] to [date], then it wouldn’t matter what time zone is set on the [date] field.
In the sample table, {Converted Time} uses the formula DATETIME_FORMAT( SET_TIMEZONE({Given Time},{Time Zone}) ,'YYYY-MM-DD HH:mm')
The resulting string will always be given in your local time zone, but internally, it appears to be labelled as GMT, since if you then use DATETIME_PARSE on {Converted Time} and then set that field (I’ve named it {Parsed Time} in the table) to GMT, the two fields are identical.
The formula for the {Parsed Time} field is DATETIME_PARSE({Converted Time},'YYYY-MM-DD HH:mm')
Handling different timezones in one table
So, back to my original problem of setting up a table for events far away.
I had a few criteria:
I need to enter datetimes in their local time (i.e. the “other” time zone)
Those datetimes need to appear consistently no matter who looks at them (i.e. I need to set the field to “GMT” display).
I need to be able to convert those datetimes to my local time (i.e. the “default” Airtable time zone)
This is a minimal table you can refer to; for the steps below, start at {Start Time (Local)}, each step describes the next field to the right.
The way I got it to work was this:
Set the field to “Use the same time zone (GMT)”, then enter your datetimes (I used a helper spreadsheet because time entry in Airtable is a pain).
Figure out your time difference. You can use the formula: DATETIME_DIFF( DATETIME_FORMAT( SET_TIMEZONE({Date/Time} , {Time Zone}) , 'YY-MM-DD HH:mm') , DATETIME_FORMAT( SET_TIMEZONE({Date/Time} , 'Europe/Reykjavik') , 'YY-MM-DD HH:mm') , 'hours')
(You can reverse the order of the two time strings if you want to skip a minus sign when you calculate the GMT time.)
To get the UTC timestamp, use the formula: DATEADD( {Start Time (Local)} , -{Time Difference} , 'hour' )
(Don’t forget the minus sign!)
You’ll need to check to make sure it’s set to “Use the same time zone (GMT)”.
To get the user locale timestamp, duplicate the preceding… but simply toggle off the “Use the same time zone (GMT)” setting!
Other notes
Obviously, you can do this all in one fell swoop, I’ve kept the fields separate for easier demonstration of the steps involved.
And even more simply, if you know the time difference, there’s nothing stopping you from using that directly in the last formula. The reason I went to this trouble is because I’m dealing with both places that do observe DST and those that don’t.
I cheated a little in the instructions above: if you use a lookup, like I did for the time zone identifier, you’ll need to convert the lookup array to a string: ARRAYJOIN( {Time Zone},"" )
You might notice that Europe/Reykjavik actually doesn’t exist in the Airtable time zone list… it’s Atlantic/Reykjavik. The SET_TIMEZONE function will default to GMT/UTC as long as you enter something for the [tz_identifier] argument.
If you’re managing attendees to the event and you need to track their arrival and departure times, you actually don’t need to do any conversion when doing comparisons to the event timestamps (e.g. when you want to know if someone will be present for a particular meeting) as long as the arrival/departure timestamp fields are also set to “Use the same time zone (GMT)”.
The ‘ZZ’ format specifier causes DATETIME_FORMAT() to return the difference between local time and UTC as positive or negative hours and minutes, like so: '-0700'. To apply this shift, the formula converts that string to a numeric value and then divides it by 100, eliminating the minutes; the resulting value is then added to NOW() using DATEADD().
@Andy_Lin1: Nice write-up of the use of SET_TIMEZONE(), which I still end up applying almost at random within a date formula until I luck onto the correct spot. Dunno why that is so impossible for me to remember — but your explanation may finally have it sunk into my head. :winking_face:
Nice shortcut using the ZZ modifier! I completely missed it even though I was looking at the documentation as I was writing the original post.
And yeah, for some reason, I had a really hard time wrapping my head around time zone handling in Airtable, but with editors in other time zones, I knew I had to get a proper working solution, otherwise we would be making errors without knowing it, since we can’t audit other time zones (I still don’t know where Airtable gets its user region/locale information, since on another account I sometimes show weeks starting on Monday vs Sunday).
Hi, @Andy_Lin1! This is amazing - it’s really helped get my head around it, and saved me a lot of work.
I have a problem that the conversion doesn’t allow timezones in half-hour increments, like ‘Australia/Adelaide’ (UTC+9.5). It’s instead rounding up to a time difference of 10 hours.
Is this a bug with Airtable or is there something I can do with my formulas to get it to display accurately? I’m using the conversion method pretty much exactly as you have when handling different timezones in one table.
Oh, that’s a good question. Technically, you might be able to consider it a bug that DATEADD or DATETIME_DIFF only work with integer values. And it’s something I would report to the devs. And you can isolate the problem to those two functions because using
SET_TIMEZONE with DATETIME_FORMAT gives the correct result.
A current workaround would be to use minutes instead of hours as the unit. You can either modify my clunky formula in the first post to use minutes, which is a bit more straightforward in this case.
@W_Vann_Hall’s version can also be modified, but I’m not sure what the best way to do it would be, since you’d have to split the ZZ modifier into its constituent hour and minute parts; or the hideously inelegant
DATEADD(
NOW(),
VALUE(SUBSTITUTE([existing second argument],'.3','.5'))*60,
'minutes')
It’s the morning of the 1st of August at time of writing, and the above formulas return the month as 7, not 8. I’m hoping someone can help me out with a formatting workaround - and regardless, I’ve flagged to AT devs cause this would catch a lot of people out unless they logged in and checked.
