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)”.
Thanks so much @kuovonne - that seems to respect the timezone correctly. Now I have to figure out how to integrate that working element into my “Last Month” filter column;
Per the documentation, the SET_TIMEZONE() function only works in conjunction with DATETIME_FORMAT(), and I’ve confirmed this in a few tests. Its results are unpredictable when used with other datetime functions. For example, MONTH(TODAY()) correctly returns 8 for me, but MONTH(SET_TIMEZONE(TODAY(), "America/Los_Angeles")) returns 7.
You got lucky with that formula, but it’s not going to work correctly for the long haul. First off, you’re taking two formatted dates—each of which is a string—and trying to subtract one from the other. Normally a string cannot be subtracted from a string, but when the text of the string is a number, Airtable auto-converts the string to a number, so this technically works. However, there are two problems that you’re going to eventually run into when the year rolls over:
It will add 100 to the calculation (going from 21 to 22 for the first two digits).
It’ll also be off because the month value goes to 1 from 12, so the math won’t work.
The recommended way to compare dates is using the DATETIME_DIFF() function:
DATETIME_DIFF({Date}, TODAY(), "months")
Note that this returns a rounded value, so you won’t get portions of months like 4.2 for example.
In theory there’s still the timezone difference to take into account, which would technically require a formula like this to convert TODAY() to my local timezone:
However, my experience shows that SET_TIMEZONE is nestable inside other date/time formulas, and I think that the documentation is incomplete.
The behavior you describe is the behavior I expect. You are seeing these results because TODAY() actually creates a date/time object with a time of midnight at GMT. At the time of these posts, TODAY() would return August 1, 2021 at 12:00 midnight GMT.
Thus, MONTH(TODAY()) would give you 8, because August is the 8th month and you are still using GMT.
However, because Los Angeles is seven hours before GMT, it would be July 31, 2021 at 5pm in Los Angeles. Thus MONTH(SET_TIMEZONE(TODAY(), "America/Los_Angeles")) would be in the 7th month.
I do admit that I took the easy approach in using only the NOW() formula in my previous post, precisely because I didn’t want to have a long explanation about timezones and TODAY() and GMT.
No worries. Timezones are one of the trickiest things to deal with in Airtable. And the fact that TODAY() actually has a time associated with can be unexpected.
Take that nap. You’ve earned it, and it’s a Sunday afternoon. Thank you for all the hard work you put into these forums.
Yah, it is a “lucky” kind of formula, but the results carry two important properties;
Date stamped records from both the same month and year have the same result as each other.
Each result progresses the same direction in size. It doesn’t matter that they’re not sequential, but only that they can be sorted and return the correct order, and effectively Grouped. The only problem with grouping with this field is the weird looking return is always showing, which is annoying and where a return that is sequential would be desirable, so the user can see that a -7 (or 7) would indicate the record is from “7 months ago”, but not from a rolling day, but rather within a month that was 7 months ago (at time of writing, any date within January 2021).
@kuovonne thanks for noting the Now() function. When I was troubleshooting early morning yesterday, I did switch between Now() and Today() but couldn’t see if either/or make a difference. I’ll work to implement a refined formula that does utilise Now() and see if the problem is then solved for next month.
From running through this problem, I would suspect that what I’m doing isn’t really… unique, or uncommon? I’m sure there would be countless users who simply need to arrange their records into groups that fall within weeks, months or years. I sometimes wonder if I’ve simply overlooked some overly obvious magic method in Airtable that allows me to just do what I’m trying to do here, but again, it doesn’t appear so - and it’s where I’m so very grateful to have other users within the community that can assist with this.
Now, to chase the magic unicorn and see if I can get a sequential month difference formula that respects my local time zone and executes at midnight on the morning of the 1st of each month, allowing the user to glance at a record and see that it’s within ‘n’ number of months ago, be it 5 months, 13 months, or 112 months… :grinning_face_with_sweat:
EDIT: Ok, started looking into this with a little spare time I have, this is very important to note - Now() respects local time, Today() however doesn’t, regardless of GMT toggle.Today() is definitely not today!
In my experience, both NOW() and TODAY() return GMT time, and can be formatted to display in local time. The main difference is that TODAY() returns the same day, month, and year as NOW() but at GMT midnight. Thus, TODAY() will have an offset from midnight based on your local time zone, which could be either yesterday, or tomorrow, depending on where you are.
Thanks for clarifying that point about the rolling day being an issue with your desired use case. Try this formula and see if it gets you where you want to go:
This levels the date comparisons by forcing both dates—the date from your {Date} field and NOW()—back to the start of their respective months, and then returning the number of months’ difference between the two. I also tried this with timezone corrections made to both {Date} and NOW() and didn’t see a change, although based on my timezone that change wouldn’t be apparent for me until later this afternoon if it even comes into play. Anyway, take it for a spin and let us know how it works.
We work across different time zones and one of the things we have been doing is trying to set up our calendar such that times for events show up on the work Google Calendar. However, Airtable seems to be adding and sometimes subtracting minutes to the time so if anyone has a solution, please help! I just set a time to 1600hrs and it’s reading as 1605hrs instead of 1600hrs. It’s also landing in the wrong time slot on the Google Calendar.
Hello and welcome to the forums! You may want to start a new topic for your question – I got a notification because I started the topic, but I’m not sure if anyone else will see it.
To address your question, I haven’t encountered your precise issue myself and I can’t find anyone else on the forums who’s had this problem. When you start a new topic, specify whether you’re using the Google Calendar sync integration (which you set up by clicking on a table tab and syncs an entire table) or if you’re using an iCal share link (which you set up by click on the share button in a view and sync just that one view). It will help people narrow down the possibilities and ways to troubleshoot the issue. Alternatively, you may want to reach out to Airtable support directly (click on the question mark button in the top-right corner when you’re in your base).
