Nov 25, 2017 08:39 AM
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:
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).
Enter the appropriate time zone. You can find a list here:
https://support.airtable.com/hc/en-us/articles/216141558-Supported-timezones-for-SET-TIMEZONE
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
ARRAYJOIN( {Time Zone},"" )
May 09, 2018 05:37 PM
Another way to restate a local time as UTC (for comparisons or whatever) is this
DATEADD(
NOW(),
VALUE(
DATETIME_FORMAT(
SET_TIMEZONE(
NOW(),
'America/Los_Angeles'
),
'ZZ'
)
)/100,
'hours'
)
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:
May 09, 2018 07:08 PM
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).
Feb 13, 2019 10:27 PM
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.
Thanks!
Feb 14, 2019 01:32 AM
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')
Hope that helps you out!
Feb 14, 2019 01:53 AM
Legend!
Changing the end of the argument to …‘minutes’)/60 solved it instantly.
Jan 20, 2021 01:56 AM
The full formula that worked for me to convert Time in TZ to GMT and it also works for not integer offsets:
IF(AND({Start Time},{TZ}), DATEADD({Start Time}, DATETIME_DIFF(SET_TIMEZONE({Start Time} , ‘GMT’), DATETIME_PARSE( DATETIME_FORMAT( SET_TIMEZONE({Start Time} , {TZ}) , ‘YYYY-MM-DD HH:mm’) , ‘YYYY-MM-DD HH:mm’), ‘minutes’), ‘minute’))
It calculates diff between TZ and GMT in minutes and add it to the date.
Apr 22, 2021 07:03 AM
Just wanted to say thank you for this thread as it helped to solve a major issue for me in a more elegant way that I was proposing. Cheers!
Jul 31, 2021 04:39 PM
I’m hoping I’ve missed something simple, but I’ve noticed that as the month rolls over that the below seems faulty;
MONTH(DATETIME_FORMAT(SET_TIMEZONE(TODAY(), ‘Australia/Sydney’)))
MONTH(DATETIME_FORMAT(SET_TIMEZONE(NOW(), ‘Australia/Sydney’)))
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.
Jul 31, 2021 04:49 PM
@Karlstens I suspect that part of your problem is that your formulas do not tell DATETIME_FORMAT
what format to use. Try this formula.
MONTH(SET_TIMEZONE(NOW(), 'Australia/Sydney'))