New Beta: More time zone formatting options for datetime fields

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?

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:

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!

Thank you, @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.

just to clarify. I won’t be modifying the date time fields, airtable will, right? In other words, this is a change Airtable will implement on a specific day, without action from me, that will modify all the date/time fields.

@John_inNJ I see - without any action on your side, any existing date time fields won’t be updated and no cell value will change either. This feature simply allows users to configure date time fields with additional time zones if that’s desired.

Glad to see these new datetime features in the pipeline! They’ll be super useful.

That said, I’m noticing an error in the API when creating records with datetime fields (via pyairtable). The datetime appears to be formatted correctly, but returns an INVALID_VALUE_FOR_COLUMN error:

Error: {

    'type': 'INVALID_VALUE_FOR_COLUMN',

    'message': 'Cannot parse date value "2022-10-03T12:40:00.000+00:00Z" for field Scheduled start time'

}

Do you know why this might be? Is this a known issue?

Error: {

    'type': 'INVALID_VALUE_FOR_COLUMN',

    'message': 'Cannot parse date value "2022-10-03T12:40:00.000+00:00Z" for field Scheduled start time'

}

That is not valid ISO 8601 formatting for the date & time. It should look something like this:

2022-09-20T16:32:11+00:00

Here are the 3 acceptable ways of formatting date & time with ISO 8601 formatting:

2022-09-20T16:32:11+00:00



2022-09-20T16:32:11Z



20220920T163211Z

That is great news indeed, @Elena_Jia ! I’d like to second what Scott wrote–we heavily use Date fields and formula fields with dates only (no times), and they still sometimes cause confusion due to timezone differences. It would be great to see this update in fields that solely have dates.

Yup, I’ve been wanting local date only fields for many years now. Caused me much grief when first learning the product and how to code - lucky Airtable has great support staff who were able to help me out.

Good morning @Elena_Jia

We’re finding the inability to apply a timezone to a Date Only fields a problematic - in our current scenario, we have a Time Line interface and Time Line views tied to Date Only fields, and we just spotted that the Time Line acts on GMT, and not our local time.

Problem being, we don’t know what ramifications we’ll get if we enable “Date Only” fields to include Times, just so we can fix the Time Zone issue. The date fields have Automations tied to them and are exporting their details via JSON… etc.

It confuses the users we’re training, when we have Date Only structure, that’s showing time time 00:00 and 23:59 - we’re really just needing to set the Time Zone on Date Only.

EDIT: Oh, and most importantly, users are working off a Timeline at 8am that shows them the date is still yesterday - that then changes to the correct day at what-ever the offset time is. It’s most troublesome.

Everyone, be sure to also leave your feedback at the form below, in addition to this thread.

Thank you all for your feedback so far!

Just a quick update for bases using the beta with formulas- On October 31, we’ll be making changes to how to how the IF formula handles custom time zones. Formulas that evaluate field directly such as IF({dateTimeInLosAngeles}, "true", "false") will output true rather than #ERROR! when the cell contains a value.

With the new timezone information set, this means we no longer need SET_TIMEZONE(Start, 'Europe/Berlin') in formulars that use DATETIME_FORMAT()?

e.g. I need this when I want to correctly display the week number in german: DATETIME_FORMAT(SET_TIMEZONE(Start, 'Europe/Berlin'), 'W')

Otherwise on some dates with time set to Monday 0:30 It will report as the week before, as it does not take daylight savings into account. I would assume that this new property make this additional SET_TIMEZONE() redundant, right?

Is it also possible to read the timezone that was set via the REST API?

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 HHSS', '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 HHSS'), '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.

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

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

How will this affect being updated by Zapier?