Help

Issues with time zone when syncing datetime field across bases

Topic Labels: Sync
1024 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Daniel_Jones
4 - Data Explorer
4 - Data Explorer

Does anyone experience time zone issues when syncing date/time fields across bases?

In one of my bases, I have many date/time fields that I’m syncing over to a different base. Those date/time fields have GMT selected in the original base. However, when these fields are synced across to different base, those fields became single line text fields instead. Thus, I have to use DATETIME_PARSE on them, so I can perform calculations on them later on. The following is an example of how I parse a synced date/time field:

DATETIME_PARSE({Synced Date}, “M/D/Y h:ma”)

I am in California, and I found out that my newly created fields may sometimes jump 7 hours forward or backward whenever AirTable syncs any new data, so I know that it is the time zone that is causing the issue. I’ve tried to manually assign the time zone on them but it doesn’t solve the issue:

SET_TIMEZONE(DATETIME_PARSE({Synced Date}, “M/D/Y h:ma”), “America/Los_Angeles”)

I’ve also played with setting the newly created fields with or without GMT selected, and I also make sure that I use the SET_TIMEZONE function whenever I handle dates/times in my other fields, but the problem continues to exist.

This is driving me nuts because I want to configure some automations to go out at a certain time (using the datetime difference down to minutes from now), but the automations are being triggered at the wrong time because the hours change whenever AirTable syncs data across bases. I wonder if anyone experiences similar situation?

1 Reply 1

Here’s one way around the problem. In your source table, make a formula field that forces the date into a string without any specified formatting. That can be done by concatenating the datetime with an empty string:

{Date Field} & ""

Here’s an example of how that looks in a field that I named “Raw”.

Screen Shot 2021-10-01 at 10.08.52 PM

When that syncs to your destination base, all you need to do is run that raw string through DATETIME_PARSE() without any format specifiers, and you’ll get the original date.

DATETIME_PARSE(Raw)

Screen Shot 2021-10-01 at 10.11.47 PM

In my case, {Date} and {Back to Date} are not using the GMT option, but as long as they’re both the same, it shouldn’t matter if you use the GMT option or not.