Changes to date handling in scripts

[Peter_Borg]

A few months ago I received an email from product@mail.airtable.com descibing changes to handling of dates and timezones in scripting. Do I recall correctly? Can someone point me to the info it described?

I’m still doing manual timezone adjustments in scripts but think this new feature can handle it properly???

[kuovonne · Accepted solution]

Are you reading or writing date/time fields? I

Date/time fields are always stored in GTM, thus in a script, the date/time is always in GMT. You can get the timezone from the field options.

If the script is using getCellValueAsString(), the result will be in the timezone for the field.

If the script is using getCellValue(), the result will be in GMT.

If the script is writing the cell value and you have a JavaScript date object, continue to use .toISOString() to generate the ISO 8601 string. Or if you use another string that is ambiguous, Airtable will try to interpret the string. Your results might vary depending on whether or not you have the timezone settings enabled.

This community post is about the beta related timezones and has good info about the upcoming change.

[Peter_Borg]

Thanks @kuvonne still a little confused though I solved my problem by working out the offset in the base and reading that into the script for use.

Meanwhile when I tested out this script it displayed the date in GMt+11 (where I am). Did the same whether the ‘Use the same time zone (GMT) for all collaborators’ toggle was set or not.

let recordId = input.config().id
let testTable = base.getTable('temp');
let record = await testTable.selectRecordAsync(recordId);
let dd = new Date(record.getCellValue('play'))
console.log(dd)

CONSOLE.LOG

1. Thu Oct 27 2022 19:04:00 GMT+1100 (AEDT)

[kuovonne]

Peter_Borg:

let dd = new Date(record.getCellValue('play'))
console.log(dd)

Notice that you are not console logging the actual value returned by .getCellValue(). You are converting the cell value to a JavaScript date object, and then console logging that date object. Try
console.log(record.getCellValue('play'))
to see the actual cell value, which is an ISO 8601 formatted text string.

[Peter_Borg]

Yep, here’s the 2 alternatives.

CONSOLE.LOG

1. Thu Oct 27 2022 19:04:00 GMT+1100 (AEDT)

CONSOLE.LOG

1. “2022-10-27T08:04:00.000Z”

The second is the actual cell value. But it doesn’t show timezone, which was my original concern. I’m thinking the developer just needs to be responsible for timezone at all times (in the database, into the script, manipulated in the script, wiritten back to database), and don’t expect the different pieces of software to guess what you want. My problem was just such a maniupulation. It did my head in keeping track of timezone at each steps and making adjustments. Maybe I made adjustments just to offset my earlier adjustments!

[kuovonne]

Peter_Borg:

The second is the actual cell value. But it doesn’t show timezone, which was my original concern.

The cell value does not store timezone. The timezone is applied by the user interface based on the field options.

The timezone in you date object comes from your computer, which is why it has your local offset.

In general, when dealing with times in Airtable scripts, I prefer to start with a date/time field and then add an interval so that there are fewer timezone headaches. There are still some when daylight savings time changes, but determining timezone offsets is a pain because they differ depending on the time of year.

[Emar_Vegt1]

Trying to write 

new Date().toISOString()

 triggers an error. Apparently the API does not want the time part to be there, even though it's according to ISO spec. Chopping off the time part works.