How Airtable manages Date and Time from an API response

[David_Ohanessia]

Hello everyone,

I’m struggling with a Date/Time situation that is maybe pretty straightforward for a lot of you but…I’m confused :winking_face: I’m definitely in need of help here ! Thank you.

So, I’m updating a table with an API call (using Data Fetcher) from which I get, inside the response, a Date/Time field which looks like this :

And when it’s pasted into a Date Field into AirTable, the Time is offset and not correct :

I don’t get it. I understand from lots of discussions here and there that it may come from how Airtable manages Date and Time but even going through some posts or support articles, I don’t know how to handle it…

Does anyone of you have an idea on how to fix this ?
This would be very helpful.
Thanks
D

[Justin_Barrett]

David_Ohanessian:

when it’s pasted into a Date Field into AirTable, the Time is offset and not correct

What you’re seeing in Airtable is that time converted to GMT, which is how Airtable stores the time internally. If the time is 19:00 in Paris, which is GMT+1 (the “+01:00” at the end of the timestamp indicates this), then the time will be 18:00 in GMT, which is how Airtable is displaying it to you. In the end, the converted time is accurate, but it’s obviously not what you want to see.

Do you want to see the time as represented in the timestamp—i.e. 19:00, as it would be in Paris—or something else? Speaking of timezones, what is your local timezone? That’s going to play into the final solution.

[David_Ohanessia]

Thank you for your response @Justin_Barrett !!

Yes, I would like to display the time as represented in the timestamp (19:00) as it would be in Paris. My local Timezone is Europe/Paris.

Thank you very much in advance for your help.
Best,
David

[Justin_Barrett]

@David_Ohanessian Thanks for the update. The first thing to check is the formatting of the date field where this date is being saved by Data Fetcher. Open the field settings and change to the Formatting tab. My gut says that the “Use the same time zone (GMT) for all collaborators” is switched on, which would explain why it’s displaying the time as GMT. If that’s the case, switching that setting off should force it to display in your local time. If that switch is not on, let me know and we’ll go a different direction.

On a related note, do you need to perform any further date calculations based on that date field, or is the date for display only?

[David_Ohanessia]

Hi @Justin_Barrett !

Thank you for the follow-up !

I’ve tried several things in order to get things right, and switching off the “Use the same time zone (GMT) for all collaborators” was on of them. Unfortunately, it didn’t work. I’m using this field for various calculations and stuff on other tables, and, you’re right, as the Date/Time is correct, every automation or programmed zaps work properly.

I would like to display only the correct Date/Time so I tried to create a formula on a separate field with SET_TIMEZONE(MyDate, ‘Europe/Paris’) and, weirdly enough, it works (!?) but only if I’m disabling the "“Use the same time zone (GMT) for all collaborators” ONLY on this formula field (see below) while it doesn’t have any impact on the original or any related field :

I don’t get the logic or the rationale behind this, even though I understand that Airtable stores Date and Times in GMT.

Best,
David

[ScottWorld]

My training video & sample base might give you some insights into how Airtable handles time zones (although my video doesn’t cover the API):

Airtable

Time Zones: Working with multiple different time zones in Airtable - Airtable...

This Airtable base solves the problem of events taking place in different time zones, and users wanting to know what time those events are taking place in...

[Justin_Barrett]

David_Ohanessian:

I don’t get the logic or the rationale behind this, even though I understand that Airtable stores Date and Times in GMT.

The SET_TIMEZONE() function is normally used in conjunction with another datetime function—most commonly DATETIME_FORMAT()—as a means of taking a GMT-based datetime and shifting it based on another timezone before executing the enclosing function. However, your test proves that it can also be used on its own. It’s giving you exactly what you want, so I’d say run with it! You can now use that converted datetime in other formula fields, but I’d hide the one that’s populated by Data Fetcher and only use the converted one.

[David_Ohanessia]

Thank you for your time and advices @Justin_Barrett
All the best,
D