DateTime/Date Field Woes

[Jeremy_Oglesby]

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:

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:

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?

[Kamille_Parks]

Ran into this today, my janky solution:

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

[Jeremy_Oglesby]

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

[Kamille_Parks]

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

[Jeremy_Oglesby]

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

[M_k]

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:

Using API to link a date field in local format API

Hi @Mike_Wilson - yes this does look like a bug. I’m getting the same behaviour as you when creating a linked record via the API (using the record id for the calendar record): [Screenshot 2020-07-21 at 17.29.33] The linked field has no format options and doesn’t seem to pick up the underlying format options of the date field. Side note, if you change the format in the root table, then all of the dates reset their format, but this isn’t a very usable workaround: Format reset to ISO: [Screen…

Mary Kay

[Justin_Barrett]

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.)

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

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.

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

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.

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

[Justin_Barrett]

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.

[Ambroise_Dhenai]

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.