New Beta: More time zone formatting options for datetime fields

[Elena_Jia]

Hi everyone!

Update 2022/11/02 - The new time zone formatting options for date time fields are now generally available!

We’re excited to announce a new beta feature that allows you to specify a display time zone of your choice on date fields that include time and formula fields that output a datetime value. Once you specify a time zone for a field, this time zone will be visible and used for all collaborators.

We’ve heard your feedback that time and time zones can be tricky to manage, and we hope that being able to choose from a broader list of global time zones can make collaborating across time zones much easier and clearer for you and your teams.

You’ll only be able to use these field-level time zone settings in your base if you’ve signed up for the beta and you can sign up for the beta via this form. As you’re trying out the feature, please submit any feedback/issues here.

Read on for more information!

For datetime fields, you can access this feature by going to the field configuration menu and toggling on both “Include time” and “Use the same time zone for all collaborators”. Setting custom time zones is only supported for fields that include time.

For formula fields, you can access this feature by going to your field configuration menu and navigating to the Formatting tab.

This feature is still in development, so there are some known gaps and aspects may be subject to breaking changes. Please keep this in mind and be judicious on how you use this beta feature in your bases.

Current known gaps include:

• Syncs involving new time zones are currently based on the displayed time. They will be based on the underlying timestamp instead. [update on 9/9/2022 - syncing a date field value from one base to another is now based on the underlying timestamp, for example 8/1/2022 5:00 pm GMT will be synced to 8/1/2022 10:00 am PDT]
• Setting display time zones on mobile is not yet supported.

Please don’t hesitate to reply in this thread with any questions, and thank you in advance for your time and consideration.

[Kamille_Parks]

Timezones are ultra annoying to manage with cross-state teams. This could be super useful!

[Karlstens]

Excellent :alarm_clock: Can’t wait to try this out.

[Sarah_Coury]

Amazing!! A much appreciated update that’ll work wonders in keeping the whole team on the same page.

[kuovonne]

This is a great new feature!

Can you please clarify if this affects the REST API or scripting? When retrieving or setting date/time values with a custom timezone, will the underlying value still be GMT or will they be in the custom timezone?

[egordin]

This is a great feature - thank you and the Airtable team!

Why can’t we set the timezone for date fields without times. I know that sounds weird, but we use date (only) fields for task due dates, and then adjust those dates in other fields with formulas. The fact that date (only) fields must be in GMT then messes up offsets due to timezone differences.

[Karlstens]

This has been one of my biggest source of data bugs I’ve encountered through my entire use of Airtable over the years - in that, to have a Date Only column, it’s locked to GMT as the timezone until you display the time… but this isn’t desired for what’s meant to be a date only column… so then a workaround formula column is needed to address this problem of showing a date only from a Date Time column.

It would be good to see the end of this confusion of Date only columns knowing what timezone that they persist in.

[ScottWorld]

Thanks so much for this, @Elena_Jia! This is a very powerful & exciting new addition that will hopefully solve at least half of the time zone problems that people have with Airtable! :grinning_face_with_big_eyes:

1. However, one big missing piece of functionality here is that normal Date fields (without the time displayed) need to gain this same functionality as well. As it stands now, Date fields (without the time displayed) create the same headaches for people as Time fields do, because people can’t control how the actual date is seen under-the-hood. Ironically enough, somebody just posted about this very problem a few hours ago in this thread.

2. It seems like you made a grammatical error in this sentence — could you please clarify what this means? "Syncs involving new time zones are currently based on the displayed time. They will be based on the underlying timestamp instead."

Thanks again for adding this into the product! :grinning_face_with_big_eyes:

[kuovonne]

I am pleased to see that the custom time zone also is listed when creating filters for views and conditions for automations.

I also really like how the time zone picker shows the UTC offset.

I like how the time zone is stored in the field options, as revealed by scripting.

It looks like the underlying format for storing the datetime value is still GMT/UTC, as displayed by a formula field and scripting. So, I’m guessing this has no impact on using the REST API or Scripting.

I would also like to have custom time zones apply to date only fields.

[Elena_Jia]

Thank you, Kuovonne!

In public APIs, when writing a date string to a column with custom time zone, ambiguous inputs like “2020-09-05T07:00:00” and “2020-09-08” will be interpreted to be in the field time zone, and nonambiguous inputs like “2020-09-05T07:00:00.000Z” (GMT) and “2020-09-08T00:00:00-07:00” will be interpreted correctly as is.

Reading a date time cell value will still return an ISO 8601 formatted date in UTC like “2014-09-05T07:00:00.000Z”.

And yes, the underlying format for storing the datetime value is still GMT/UTC. :grinning: For REST API, we can potentially set cellFormat as string to obtain date time corresponding to the custom time zone; and in Scripting, getCellValueAsString will achieve this as well.

[kuovonne]

Elena_Jia:

In public APIs, when writing a date string to a column with custom time zone, ambiguous inputs like “2020-09-05T07:00:00” and “2020-09-08” will be interpreted to be in the field time zone, and nonambiguous inputs like “2020-09-05T07:00:00.000Z” (GMT) and “2020-09-08T00:00:00-07:00” will be interpreted correctly as is.

This sounds perfect! Thank you for digging into this and sharing the results.

Another question – how does this interact with forms?

I created a form with a custom timezone that is not my local timezone and went to fill it out. I clicked the “Today” button in the picker, which normally gives me the current date and time. Clicking that button still looks like it gives me the “current” date and time, but it is the current date and time for my local timezone, not for the timezone set for the field.

I’m not sure what the expected behavior should be. It would be a bit weird for me to click “today” expecting the current date/time and see a different date/time. However, in order to actually store the current date/time, it would need to be a different time.