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)”.
Are you using DATETIME_FORMAT? If so, are you setting the minutes format with “MM” or “mm”? “MM” is used for the month, so if it is May it’ll return 05, instead of 00 (with “mm”). This was a gotcha for me!
