Help

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

Solved
Jump to Solution
566 1
cancel
Showing results for 
Search instead for 
Did you mean: 
primeBias84
4 - Data Explorer
4 - Data Explorer

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.

 

Screen Shot 2023-04-23 at 7.01.41 AM.png

Screen Shot 2023-04-23 at 7.02.46 AM.png

Screen Shot 2023-04-23 at 6.59.37 AM.png

   

1 Solution

Accepted Solutions
AirOps
7 - App Architect
7 - App Architect

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!

See Solution in Thread

3 Replies 3
AirOps
7 - App Architect
7 - App Architect

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
18 - Pluto
18 - Pluto

@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...

Thank you, this worked!