Birthday field, user-submitted form data, everything off by 1 day

[lorenb]

Here's the story:

A few years ago I set up a base to capture birthday information for school applicants. Users would fill out a form (everyone using it was on EST), and enter a birthday (date only, no time).

A year or so ago I started getting reports that some dates shown in our dashboard interface were off by 1 day. E.g. if the entered birthday was Dec 10, the interface would show Dec 9. (This may have correlated with when Airtable made some changes to the way they handled dates/timezones internally?)

I'd like to fix this "correctly", so I'd really like to understand what is happening behind the scenes.

When users submit a date with a form, does the user locale or browser timezone have any influence on the submitted date? Did the behavior of form-submitted dates change when Airtable made date handling changes?

Are dates still stores as ISO strings (with timestamps) even if we don't want/have any time information?

How can I correctly render a date submitted with a form that isn't potentially mangled by timezone transformations? E.g. "Dec 10" should be selectable on a form by anyone, anywhere in the world, and "Dec 10" should show up in our interface, no matter what time zone the viewer is in.

I can appreciate this is actually more complicated than it seems at first glance, but I'd love to set this up right. Naively trying SET_TIMEZONE in a derived column didn't do the trick, and it felt like I might just be playing whack-a-mole in any case.

Thanks!

[lorenb]

OK, here's some additional curious behavior. Here's a TEST column derived from the submitted dates, the formula is just {Student Birthday} so it should just be a passthrough — and the field is set to show everything in the same timezone for everyone.

The curious thing is that some dates show up as EDT, and some as EST. Which means perhaps whether daylight savings was active at the time the form was submitted is somehow stores along with the original date?? And that hour could kick some dates into the wrong day when we display them later?

[lorenb]

(Also, to clarify, the dates in the TEST column are actually wrong by *2 days*, the real issue is that the dates in the Student Birthdate column are also wrong, by 1 day, and I have no idea why).

[Databaser]

Yeah, date and time can definitively give some headaches...

Options for displaying dates

All dates are stored in Airtable in Greenwich Mean Time, or GMT. But, how you choose for them to display in your base is a field-level setting that will affect all collaborators. You can have dates display either:

1. In collaborators' local time, so the datetime will appear different for viewers in different timezones, adjusted from GMT
2. As the same for everyone, done by switching on the "Use the same timezone for all collaborators" toggle shown below, in the field's customization menu.

Does this article helps you any further?

[lorenb]

Thanks so much. Yeah I understand it's a field-level setting, but it doesn't seem like I have an option to display dates with a given timezone (only date+times). Is it possible this is glitching out because a given date (represented internally as a datetime) is somehow straddling the day boundary?

How does SET_TIMEZONE actually work? Does it actually shift the time from GMT->specified zone, or is it somehow "reinterpret casting" it?

It still makes no sense to me that the dates submitted on the form don't actually match the data in the "Student Birthday" field. Even if I don't do anything to it, the dates are wrong by a full day.

[Vikas_Vimal2]

Computers always work in UTC. It is only when they try to format it for humans do such issues arise. 10 Dec is counted in UTC as the start of the day at midnight. It’ll still be 9 Dec in US at the time. Which is the correct time, objectively. That is also the correct behaviour when you want to track a time stamp by the minute. When someone selects a date field, they pass the time as per their timezone. There’s no way to correct that without knowing which timezone there referring to. 
but if you wish to see the correct time for New York, just add the time difference to the UTC time. Me mindful of daylight savings, or just use the UTC timeline for all date time field. 

[lorenb]

Interesting, thanks. I think there's still something funny with the date capturing from a form though. I have an example here where a user (on eastern time) submitted a form at 8:18pm ET with the date "Dec 10 2018", and when I view the date in Airtable, it shows up at "Dec 9 2018 GMT". This seems to be backwards from what you're describing, if anything I would have expected it to end up on Dec 11 (Dec 10+8pm+4hrs => Dec 11). Very strange.

Appreciate the help!

[Databaser]

I think it always converts to GMT (so changes the actual data) under the hood. If you want to see it again in ET, you have to set the timezone.