Choosing the time zone used for Date and Time functions

Chris_Rutledge · ‎Jan 01, 2016

I am in Seattle, using my local time zone, Pacific Standard Time (GMT -8). One of my columns, ‘Start Time’, contains a Date value of “1/2/2016 10:00”, but when I reference it in a separate column using

DATETIME_FORMAT({Start Date}, ‘MM/DD/YY HH:mm’)

the time displayed is 8 hours ahead (18:00). The same thing occurs for any other times I reference.

I see that I could use SET_TIMEZONE, but do I need to use this whenever I want to have the results of a formula in my time zone? Is this a setting I can change in my account or base, anywhere?

W_Vann_Hall · ‎Nov 11, 2019

Look at the second example in this reply. You can use a format specifier to extract a value you can then feed directly back into DATEADD() in order to restate your local time as GMT. I agree, it gets confusing when working with an integration timestamp. In your case, I think I’d load it into a non-GMT field, restate it into GMT, and use that value in any calculations…

Justin_Barrett · ‎Nov 13, 2019

@Sarah_Smith1 Does any of the above help in your situation? I’ve been too busy lately to dive in for a deeper look.

Just to clarify, I’m not an Airtable employee, so I can’t speak to how/if the team is addressing this issue.

Thomas_Rush1 · ‎Mar 08, 2020

Stiiiiil nothing? It’s the year 2247 and Airtable hasn’t fixed their timezone bug.

Thomas_Rush1 · ‎Mar 08, 2020

Is this still the best solution? (over 4 years later)

ES1 · ‎Apr 19, 2020

DOES ANYONE WHO WORKS AT AIRTABLE KNOW ABOUT THIS ISSUE?

We can’t ask people to convert a date to GMT when they enter it. That makes no sense.

And we can’t have people look at an event date in their own timezone, bc that makes maintenance of event times virtually impossible.

And this makes the calendar sharing virtually useless, since when we import the calendar into google calendar, etc, it shows all the wrong dates.

AIRTABLE PEOPLE, PLEASE HELP.

Serge_Lacasse · ‎May 04, 2020

Hi,
the workaround I’ve found is to:

create a new field (say DATE OK);
Make it a formula field
Use this kind of formula (this one substract 4 hours to GMT which seems to be the default Timezone in Aritable): DATEADD(Date,-4,‘hours’)
Adjust the format at will.
Hope this help!
Cheer
s

ScottWorld · ‎May 04, 2020

I’ve created a sample base & accompanying training video to solve the dilemma of working with multiple different time zones in Airtable.

You can click on the link above to be taken to my solution in the Airtable Universe, or you can go to my post for this topic here:

sunny · ‎Jun 03, 2020

Hello again @ScottWorld and other AT experts. I see a lot of other ATers have had this issue for years. I am using the table all by myself atm, no. collabs. Is there a way to have the base/table use my local time zone (PDT) without having to update each field/formula with a DATETIME wrapper, or using a new base (referenced here)?
As always, appreciate the help and guidance.
Regards,
Sunny

Justin_Barrett · ‎Jun 03, 2020

When manually entering dates, Airtable’s default behavior is to leave them at your local timezone (according to something I recall hearing from someone at Airtable). Only when formulas come into play do things get messy. For one, the formula field’s “Formatting” tab defaults to displaying dates based on GMT, and you have to switch it off manually. That’s one thing I have to keep reminding myself when setting up new date formulas. Also, using either TODAY() or NOW() can be tricky because TODAY() is based on GMT, but NOW() is based on local time (from what I’ve been told).

Is there a specific issue you’re trying to address? If so, it might be best to start a new thread for it.

ScottWorld · ‎Jun 03, 2020

Wow!! I did not know that!! That is a hugely important thing to know about, and it’s not even documented on their formula field documentation page. You’re right — the NOW() function always returns a value based on the user’s CURRENT TIME ZONE, but the TODAY() function will always return a value based on the GMT time zone. This is a huge difference.

@sunny As Justin said, if you don’t turn on the “GMT” option, Airtable will continually update the time based on whatever time zone your computer is set to at the current moment.

So, if you’re in PST right now and you type in the time of 8:00pm, the system will always remember that time as 8:00pm PST. But, the way that Airtable works is this: If you then log in from EST, the time will then display as 11:00pm. If you have another collaborator working in EST and they type in the time of 8:00pm on their end, Airtable will remember that time as 8:00pm EST, so if you’re in PST, you will see that time as 5:00pm.

Yes, it’s strange and it’s unexpected. But once you know that Airtable works this way, you can accommodate for it.

All of this is covered in depth in my training video & sample base. In my training video, I even change the time zone a few times on my own computer to show the viewer what happens to the time fields.

To keep times consistent at all times, you really only have one option, which is to enter in the times as GMT times (by turning on the GMT option).

Then, if you need to translate the GMT times into different time zones, that’s where my sample base comes in. You’ll just need to add a few extra fields to your own base (based on my base), which will let you explicitly translate the times into different time zones.