Timezones - Not sure why script outputs GMT?

[Karlstens]

I’m not too sure where I’m going wrong. The Javascript examples I’ve read indicate that this should work, but Airtable isn’t processing them as I’d hoped - and judging from the results I’ll need to modify my script somehow.

Although the Automation triggers at the correct local time, it certainly doesn’t pass the local time as an input into the script - which isn’t a problem so long as we can easily process it to the correct time within the script, which is where I’m stuck.

//Trigger executes 7am daily and provides the _input_date to the script, but it is using yesterdays date (even though Airtable trigger states it is using local time?), so attempting to align it with local time via JS below

let rightNow = input.config()._input_date;
var date = new Date(rightNow);

let today = new Date()
let string = today.toLocaleDateString()
let correct = new Date(string)

console.log(date.getTimezoneOffset());
console.log(date.toLocaleTimeString('en-AU'));
console.log(correct)

Appreciate any help. This is messing with me big time. :face_with_raised_eyebrow:

[kuovonne]

Karlstens:

Although the Automation triggers at the correct local time, it certainly doesn’t pass the local time as an input into the script

Although Airtable displays times in local times, Airtable always internally stores date/times in GMT. And an Airtable automation does not know what your local time is because it does not know where you are. The automation runs on Airtable’s servers, which could be in any of several time zones. So it is easier for Airtable to treat all date/times as GMT and only convert to local time in the user interface.

Keep in mind that all date/time fields are stored internally as GMT, and you control whether or not they are displayed in local time with the field settings.

[Karlstens]

Ahh cool, thanks for reaffirming @kuovonne of what’s going on under the hood. So with all this in mind, and a little further reading on parsing time, I have my script almost working but with only one outstanding question;

You can see that I’ve hard coded +10 hours onto the GMT time - however the plot thickens in that I’m needing that value to accommodate both +10 and also +11 hours due to local daylight-savings. Is there any straight forward method for doing this? getTimezoneOffset returns 0 as I assume it’s calculating the offset between GMT/UTC and GMT/UTC which obviously evaluates to zero.

If not, I’m guessing I’ll need to write a function that checks if the date range is in between the first Sunday of April and the first Sunday of October…

At least my date-stamp script now stamps the correct date into records at 9am each morning.

[kuovonne]

Are you trying to fill a date field with the time the automation ran? It might be simpler to convert the field to include the time as well as the date and oass in the actual time from the automation trigger. Make sure to set the display time zone for the field to match what you want.

[Bill_French]

Karlstens:

Is there any straight forward method for doing this?

Yes. You need to extract the TZ offset based on your locale which is done by capturing the date/time and using the getTimezoneOffset() method.

var d = new Date();
var n = d.getTimezoneOffset();

[Karlstens]

Thanks @Bill.French - I wish that worked, but I suspect that the code is comparing GMT/UTC with GMT/UTC and thus returns zero. I’m hoping there’s a command where I can just feed in the AEST/AEDT value to have either 10 or 11 returned (depending on time of year).

@kuovonne - I have automation creating a new record each morning and partially prefilling it. But the date from GMT would be entered and thus new records created for today were being date stamped as yesterday due to the 10 hour time difference. The method that I’ve worked on here solves that problem, except for the fine tuning of the daylight savings time which I’m still keen to solve as it’ll pop-up again no doubt. I too initially tried Bill’s method but was disappointed to find it’s not working that I suspect is due to Airtable systems being set to GMT/UTC.

Here’s another screenshot showing the confusion;

In isolating the problem, it seems that when I call date.toLocaleDateString(‘en-AU’) that it’s;

• ignoring my local time (7:30am) and instead using GMT
• formatting the date to local date format (which is good), but then using the GMT time that changes today into yesterday which it absolutely shouldn’t be doing!

[Bill_French]

Karlstens:

I suspect that the code is comparing GMT/UTC with GMT/UTC and thus returns zero

If that’s what’s happening then your browser/PC is [incorrectly] indicating your current locale is GMT/UTC. Is that where you are located? I suspect not and here’s why your methodology is failing.

You need to extract a date/time that is unrelated to “rightNow” - try the code I pasted above. It uses a new Date() method to give you the actual time in your locale which should not be influenced by anything except your computer’s TZ location.

[Justin_Barrett]

What @Karlstens (likely accidentally) omitted was most of the left side of his interface, which would have clued us in that the script he’s writing is in an automation “Run script” action (clue #2 is the “Test” button in the upper-right corner, which doesn’t exist in the Scripting app). Automation scripts run on servers that are in GMT, whereas scripts in a Scripting app run on the local browser and return the local time. I mainly know this because I’ve run into this exact issue with some of my own scripts.

The solution that I use in the bases where I need to convert to local time in an automation script is to have another automation that runs weekly and queries a free date-and-time API to get my local offset from GMT. This offset is then stored in a [Setup] table, and retrieved by all other automation scripts in that base that need it.

[Karlstens]

Yo @Justin_Barrett thanks for this, exactly what I was needing to understand.

I’m thinking it would be great for Airtable Devs to add a new command/method into the Automated Script that allows us to run an on-the-fly time calculation so that the local GMT can have a timezone added to it. I will write them some nice feedback/request. It’s not a big issue if a users GMT offset is static, but for those of us who live with Daylight Savings (as much as I love it) it means there’s no simple solution to this problem. Take a look at the solution you’ve devised - way to go with that too, amazing work, but geez, such a level of effort just to calculate time - a feature that we’d assume would just be accessible from the get-go with minimum level of effort.

[kuovonne]

Karlstens:

I’m thinking it would be great for Airtable Devs to add a new command/method into the Automated Script that allows us to run an on-the-fly time calculation so that the local GMT can have a timezone added to it.

It is highly unlikely that Airtable will add new methods to scripting that are not specific to the Airtable architecture. There are workarounds (which may involving api calls) for most common things that cannot be done with vanilla JavaScript.

You could have a fancy formula field that calculates the current offset. The value would be the same for all records. You could then read the value from that fancy formula field.

Shucks, I decided to throw this formula field together. It is waste of Airtable resources since NOW() recalculates every few minutes to every few hours. You could replace NOW() with TODAY() and get just as good results, except when within a day of when daylight savings time changes.

DATETIME_DIFF(
    DATETIME_PARSE(
        DATETIME_FORMAT(
            SET_TIMEZONE(NOW(), "America/Los_Angeles"), 
            "YYYY-MM-DD-HH-mm"
        ), 
        "YYYY-MM-DD-HH-mm"
    ), 
    NOW(), 
    "minutes"
) / 60

If you put this formula in a special Setup table like @Justin_Barrett has, then it will recalculate only for every record in that Setup table, and not every record in the target table, so it would take slightly less resources.

I still prefer a solution where the date/time is stored properly in the first place without having to hack backwards to the correct date/time.