Help

DateTime/Date Field Woes

Topic Labels: Scripting extentions
5639 8
cancel
Showing results for 
Search instead for 
Did you mean: 

How can I ensure Airtable properly handles dates I write from JavaScript into a Date field?

This script pictured below properly inserts the Date retrieved into a Date field as long as I run it before a certain time. If I run it in the morning, for example, I get the date I expect in my Date field. I just ran this at 6:50pm in my current timezone and it entered the date into the date field as if it were a day later:

CleanShot 2020-07-21 at 18.51.10

This really has me thinking that it’s an Airtable date display problem, and not a JavaScript DateTime interpreter problem. I have the same issue with this script run as an Automation - Script Action as well.

I do not have the Time included in this Date field, and I do not have the “Use the same timezone for all…” option checked:

CleanShot 2020-07-21 at 18.58.10

So my presumption is that this field should be interpreting DateTimes relative to the timezone my browser recognizes me to be in, or that my system is set to (and clearly the JavaScript that is fetching the date and displaying it in my script output recognizes that I am in MST). But frankly, I have no clue what timezone this Date field is interpreting the Dates I pass it to be.

Does anyone know how I can ensure my dates are entered accurately relative to my timezone, or even regardless of timezone (since I am not interested in the time anyways)? Any Airtable dev’s able to help?

8 Replies 8

Ran into this today, my janky solution:

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

Have you tried inserting that date “correct” into a Date field at multiple times throughout the day?

No, I only tested within the last few hours or so. For reference, I’m in PST land

I’m in “we aren’t crazy people who change their clocks for no good reason twice a year land” :laughing:

M_k
11 - Venus
11 - Venus

Hi @Jeremy_Oglesby

There was a post earlier today along the same line, just slightly different or maybe not.

I don’t know if it will help, since it has to do with the date, but it’s the behaviour that’s interesting.

Here’s the link anyways:

Mary Kay

I found this to be slightly enlightening. I ran a test, and saw the same result that you did. Just for fun, I also retrieved the date back into JavaScript and sent the toString() version to the console again to see what would happen. (Top line is before posting to the field; bottom line is after retrieving it from the same field.)

Screen Shot 2020-07-21 at 8.56.48 PM

I then converted the field to show the time (GMT disabled)…

Screen Shot 2020-07-21 at 8.57.49 PM

I then ran my test script again. Now the field matches my local date and time, but look at the time spit out for the retrieved version. Still 7-22 like it was last time, but it’s exactly 7 hours ahead of my local time. In other words, the time retrieved is relative to GMT even though the field isn’t formatted to show it that way.

Screen Shot 2020-07-21 at 9.00.26 PM

And now when I convert the field back to be date only, it keeps the last date that it had: 7/21.

Screen Shot 2020-07-21 at 9.01.30 PM

I’ve got some slightly jumbled theories based on all of this, but long story short, I believe that a date passed from JavaScript to a date field is first converted from the user’s local time to GMT. That explains why, after a certain time of day, dates are stored one day ahead of the local day. This also explains why the date retrieved from that same field is relative to GMT.

I tested the suggestion from @Kamille_Parks, and the date stayed “local” after sending it to the field.

Screen Shot 2020-07-21 at 9.36.05 PM

I’ll run the same test tomorrow morning and see what happens.

Good news! That fix that @Kamille_Parks posted does work at multiple times of day. I ran the same script just now, and it shows the correct date. What I found especially interesting with this workaround is that when the time option is active for the field, the time no longer becomes my local time, but it stays at 12am.

Screen Shot 2020-07-22 at 9.02.54 AM

Ambroise_Dhenai
8 - Airtable Astronomer
8 - Airtable Astronomer

I ran into this while trying to sync a base with another, the column type was lost (date) and I was trying to copy the field value from a text column into a datetime column.

So far no luck, it’s not accurate. I believe it’s easier to copy the value as text into a text field and then run DATETIME_PARSE formula on another field to get it right.