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.

[Partyborn]

I also just realized, that it is impossible to generate a timezone aware datetime with DATETIME_PARSE().

Here is my usecase:
I want to find out the start of the current week (including time). I usually do this via:
DATEADD(TODAY(),-DATETIME_FORMAT(TODAY(),'E')+1,'days')
This will output me the correct date, but not the correct time. It will use UTC.

But now this time is set to UTC 00:00. If we convert it to german time, it is sometimes +1 and sometimes +2 (due to daylight savings). But I need 00:00 relative to the german time, not UTC. Aka:
DATETIME_PARSE(DATETIME_FORMAT(DATEADD(TODAY(),-DATETIME_FORMAT(TODAY(),'E')+1,'days'), 'DD.MM.YYYY') & ' 00:00:00', 'DD.MM.YYYY HH:mm:SS', 'Europe/Berlin')
or as alternative:
SET_TIMEZONE(DATETIME_PARSE(DATETIME_FORMAT(DATEADD(TODAY(),-DATETIME_FORMAT(TODAY(),'E')+1,'days'), 'DD.MM.YYYY') & ' 00:00:00', 'DD.MM.YYYY HH:mm:SS'), 'Europe/Berlin')

But that is not supported by airtable… (That syntax does not exist, only for locale, which is not a timezone)

Also comparing datetimes seems to be not timezone aware. Because if you compare e.g. 30.10.2022 6:00 with 31.10.2022 6:00 in german time, the timespan should output 1 day/24h. If you do that in UTC you should get a different result (because the time is shifted at that night boundary).

All in all I suggest Airtable to make the internal processing of the datetime objects timezone aware. But only if it is explicitly set, otherwise use datetimeunaware objects. This would solve most problems. Meaning SET_TIMEZONE() should be supported wherever used and then processed further in all airtable formulars.

I know this is a complex topic, feel free to ask me if something is unclear.

[Alex_Whitton]

Agree

I initially thought this was a bug - that it displays with a time in my timezone, yet if only as a date and referenced it will reference as GMT - very confusing

[Alex_Whitton]

I saw another community thread that directed me for to this solution:

If you reference ‘ww’ vs ‘WW’ dates it will alter from Monday or Sunday of the week start:

I use a helper column for the time reference in my local time - with this new feature this might be bypassed going to play

[n8atkinson]

How will this affect being updated by Zapier?