SET_TIMEZONE and other time-related quirks


#1

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:

  1. I need to enter datetimes in their local time (i.e. the “other” time zone)
  2. Those datetimes need to appear consistently no matter who looks at them (i.e. I need to set the field to “GMT” display).
  3. 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:

  1. 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).

  2. 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

  3. 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.)

  4. 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)”.

  5. 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)”.

Setting current time in different timezones
#2

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. :wink:


#3

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).