Help

Re: SET_TIMEZONE and other time-related quirks

3859 4
cancel
Showing results for 
Search instead for 
Did you mean: 

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)”.
23 Replies 23

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:

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

Josh_Haywood
6 - Interface Innovator
6 - Interface Innovator

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!

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!

Legend!

Changing the end of the argument to …‘minutes’)/60 solved it instantly.

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.

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!

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.

@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'))

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;

DATETIME_FORMAT(SET_TIMEZONE({Date}, 'Australia/Sydney'), 'YYMM')-DATETIME_FORMAT(SET_TIMEZONE(TODAY(), 'Australia/Sydney'), 'YYMM')

The above works, with exception to the early hours of the 1st of each month.

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:

  1. It will add 100 to the calculation (going from 21 to 22 for the first two digits).
  2. 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:

DATETIME_DIFF({Date}, DATETIME_PARSE(DATETIME_FORMAT(SET_TIMEZONE(TODAY(), "America/Los_Angeles"), "X"), "X"), "months")

However, when running that I get the exact same result as just using TODAY() uncorrected.

The documentation states that SET_TIMEZONE()

Must be used in conjunction with DATETIME_FORMAT

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.

Sorry. You’re correct. I clearly wasn’t thinking this through thoroughly this morning.

I need a nap.

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;

  1. Date stamped records from both the same month and year have the same result as each other.
  2. 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!

image

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:

DATETIME_DIFF(
    DATETIME_PARSE(
        YEAR(Date) & "/" & MONTH(Date) & "/01", "YYYY/MM/DD"
    ),
    DATETIME_PARSE(
        YEAR(NOW()) & "/" & MONTH(NOW()) & "/01", "YYYY/MM/DD"
    ),
    "months"
)

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.

Molly_1
5 - Automation Enthusiast
5 - Automation Enthusiast

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