Re: Local Time via Automation Script

Karlstens · ‎Nov 15, 2021

With interfaces and Automations on my mind, this old chestnut of local date/time has popped up again. I’m the first to admit that I’m a slow learner, but can someone help me with understanding what’s actually going on here?

//Automation Script:
const now = new Date();
console.info(now)
console.warn(now.toString())

I find it impossible to simply calculate my local date-time and stamp it into an Airtable record via an Automation script and it’s most frustrating.

When I looked at this same problem last month, I employed a god-awful hack where my Automation script looks up a table that’s soul existence is for hosting a local-time offset for the script to use… :nauseated_face: There was a very similar/identical discussion, but it turned out the Scripting App handles this local date-time issue differently to the Scripting in Automation.

Surely there’s an elegant way to achieve a local date-time date stamp into a field via an Automation Script?

Post Script: On the plus side, I’m upp’ing my dev game, installing and setting up Visual Studio Code for the first time :partying_face:

Justin_Barrett · ‎Nov 20, 2021

Correct. That’s because automation scripts run on Airtable’s servers, whereas scripts in Scripting apps run on your local machine.

I actually use the “hack” method that you described (I use that same table for some other internal settings, so it’s not a single-purpose table), but before doing that I was calling a timezone API that told me my local timezone’s offset from GMT.

If you know your timezone’s offset already (and are willing to change the code twice a year if your area recognizes some form of daylight saving time shift), you could just hard-code this into the script. Here’s a test that I ran:

const dateTable = base.getTable("Date tests")
const now = new Date()
// "Correct" the time
now.setHours(now.getHours() - 8)
await dateTable.createRecordAsync({"Date": now})

The {Date} field is set to only show a date, not the time. My local timezone (US Pacific) is GMT-8, so I take 8 hours off of the calculated time before setting it in the record. (If your field shows both date and time, you should not make this adjustment; the calculated time will actually work unmodified.)

Karlstens · ‎Nov 20, 2021

Yah, hard coding +10 hours was my first work-around, but we recently had daylight savings and it broke a couple of automations due to that very hour difference.

Workarounds aside, why wouldn’t Airtable devs want to address this issue? Date and time are so critical especially when it comes to the entire concept of automations that even have a trigger. I just find it disheartening that these bumps exist but there’s no Airtable devs to speak with about if they’ve planned a fix, a feature enhancement, or otherwise.

Justin_Barrett · ‎Nov 21, 2021

Even though we’d like to think that Airtable should know each user’s “local” timezone, remember that Airtable is a web application. While users will typically log on from a consistent location most of the time, that’s not guaranteed, so “local” isn’t guaranteed to always be the same. How then is Airtable supposed to determine the timezone difference when running automation scripts?

To more directly answer your question, even if the devs had a solution, implementing that now would likely break the behavior of an untold number of scripts that are already designed to work out the difference through other means. It’s probably safer to leave the system as-is than to incur the wrath of the users by applying a “fix” that actually breaks things.

Have you contacted Airtable support to ask them about this? They’ve always been very responsive from my experience.