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.

[Vivid-Squid]

kuovonne:

I think you mean that 9am PT on a particular date will remain 9am on that date regardless of whether the the current date is in PDT or PST.

Yes, that is what I mean, a set date field will not change when your local time changes in and out of DST.

@kuovonne Do you know if the a date field is a seconds count from epoch? 00:00:00 1/1/1970?

[kuovonne]

Vivid-Squid:

Do you know if the a date field is a seconds count from epoch?

Dates are stored internally in ISO 8601 format. If you need to know the seconds count from epoch, you can use

VALUE(DATETIME_FORMAT({date}, 'X'))

[kuovonne]

Elena_Jia:

We scoped adding time zones for date only fields out of this launch

I happened to be thinking about this when I was filling out the beta feedback form. I realized that adding timezones to date only fields is actually far more complex than it appears on the surface.

I decided to mention this here on this thread as well as in the beta feedback form for other people who may wonder why adding timezones to dates was not yet included.

I’m also just guessing here about the complexity, so feel free to correct any of misunderstandings.

• Date only fields currently do not store a timezone in the field options. Date time fields already had a timezone section in the field options that previously included only “utc” and “client”. It is much easier to add a new timezone to that existing list, versus adding timezone information where it doesn’t exist at all. Adding a timezone field option that didn’t exist at all before would have a greater impact on scripting, custom extensions, the meta api, etc. in addition to internal code.

• Displaying a dateTime field with a fixed timezone is only a display issue. It is still stored as a ISO 8601 date time string under the hood. This means that there are no underlying data conversion issues.

• Date only fields do not display a time but are actually stored as UTC midnight. Adding a timezone to these date only fields might require that the date be something other than UTC midnight. The actually timezone offset would also need to be based on whether or not the timezone observes daylight savings time and whether or not the date was in daylight savings time. This also requires special care for dates before and after the transition between daylight savings time and standard time.

• There might also be implications for date base filters, especially the “is today” filter. These filters are spread across views, automations, and interfaces.

So, thank you for making timezone formatting options available for dateTime fields, even though there are still issues to work out for dateOnly fields. I have confidence that Airtable will work through these issues and come up with an implementation in time.

[Vivid-Squid]

Daylight Savings Time is very tricky. Do we know where Airtable is getting the DST details? Around the word the rules change all the time, territories join other territories and adopt a new rule set. While other territories do not recognize DST at all. A place’s time zone UTC offset changes or does not change in accordance with other territories.

Not even the US is immune for example: Arizona does not observe DST, yet the Navajo territories within Arizona do observe DST, and there are territories within the Navajo territories that do not.

Not to mention, some places change in :30 min increments to DST.

The deeper Airtable dives into Time Zones will be interesting to watch for a global solution.

[Elena_Jia]

Daylight Savings Time is very tricky. Do we know where Airtable is getting the DST details? Around the word the rules change all the time, territories join other territories and adopt a new rule set.

Thanks for bringing this up. This is a good point! @Vivid-Squid
Currently we use the IANA timezone data to obtain zone offsets and handle date time conversions between time zones. We will need to keep our data updated in case of changes like the ones mentioned.

[John_inNJ]

Im not versed in time zone or API management. But I have a simple question. I rely on may Zapier scripts to maintain a large airtable database. Many of the triggers are based on “Modified Record” date and time field. Will this change cause zapier to trigger on every record and thus thousands of runs?

[ScottWorld]

Hi @Elena_Jia,

There seems to be a bug with automations using the new time zone formatting.

When we perform a “Find Records” action in Automations, it returns the proper time zone formatting as you can see in the screenshot below:

However, if we try to insert those found records into our next automation action (such as sending an email with a grid of those found records), everything reverts back to UTC again. Please see screenshot below:

[Elena_Jia]

ScottWorld:

When we perform a “Find Records” action in Automations, it returns the proper time zone formatting as you can see in the screenshot below:

Thank you for the thorough testing and feedback on the automation behavior! It is indeed a bit confusing when Find Records display with specific time zones and automation outputs use UTC. We will keep an eye out for more feedback on this!

The cause of this behavior is that during execution automation still uses the underlying timestamp stored in UTC when reading and outputing values (for slack/email/google docs etc and if we look at date time values in execution history). We didn’t plan to update the automation output format for time zones initially, but we will keep this in mind as an improvement in the future!

[ScottWorld]

Thank you, @Elena_Jia! :slightly_smiling_face:

[Elena_Jia]

I rely on may Zapier scripts to maintain a large airtable database. Many of the triggers are based on “Modified Record” date and time field. Will this change cause zapier to trigger on every record and thus thousands of runs?

Hi John, if I understand correctly, you are wondering if modifying the time zone of a date time field will cause the cell values being updated and triggering the scripts based on the field. That is indeed likely to happen since modifying the date time field to use a different time zone will modify the underlying timestamp in the cells.