Gmail automation ISO Date/Time timezone not matching DB table entry

[primeBias84]

Hi all, 

I have set up a gmail automation and am sending an ISO Date time to the email recipient. The date time that is sent is 4 hours ahead of what is entered in my table. Any ideas as to why this is happening?

In the screenshot of the table view, I do not have a time zone set for date_ordered. I tried with the proper timezone configured as well and it still has the same issue.

 

   

[AirOps · Accepted solution]

Hi @primeBias84, 

Welcome to the joys of working with time zones in Airtable - it can get a little messy if you don't know exactly how timezones are handled. 

In this case when you pull any field type of "Date" into an email automation the result will be an ISO formatted string in the GMT/UTC timezone irregardless of the actual timezone settings you have set in the field. 

To get around this you will want to format your date into a string for use in the email automation. You will likely need to use a combination of the DATETIME_FORMAT() function and the SET_TIMEZONE() Function. 

Your formula should look something like this: 

DATETIME_FORMAT(SET_TIMEZONE([DATE FIELD], [timezone]), "[format specifier]")

Example: 

Date: 2023-03-16 6:30am MDT

Formula: DATETIME_FORMAT(SET_TIMEZONE(date, "America/Toronto"), "YYYY-MM-DD LT")

Result: 2023-03-16 8:30 AM <-- this is exactly how it will appear n your email. 

Supported format specifiers for DATETIME_FORMAT

Supported timezones for SET_TIMEZONE

I hope this helps!

[ScottWorld]

@primeBias84 

As @AirOps mentioned above, time zones are quite the challenge in Airtable.

In addition to Cherry’s advice above, you may also want to check out my time zone base & time zone training video here:

https://www.airtable.com/universe/expL1TuVyaYaInqm2/working-with-multiple-different-time-zones-in-ai...

[primeBias84]

Thank you, this worked!