Choosing the time zone used for Date and Time functions


#1

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?


#2

I’m having a similar issue. Any update here?


#3

Hi Chris and Jack,

We don’t currently have a global setting for the base. You can work around this by either using DATETIME_FORMAT(SET_TIMEZONE(date), format) or you could check “Use the same timezone for all collaborators (UTC)” on date with time columns. In the latter case all times will be stored as UTC.

We’ll consider adding a global default timezone per base.

Alex


#4

Updated: Saw that you have to wrap the formula in the DATETIME_FORMAT function. Thanks all.
DATETIME_FORMAT(SET_TIMEZONE(CREATED_TIME(), ‘America/Chicago’), ‘M/DD/YYYY h:mm A’)

That would be great. I am having a struggle using SET_TIMEZONE on a FORMULA field. The following doesn’t change the timezone. I might be missing something. Thanks.

SET_TIMEZONE(CREATED_TIME(), ‘America/Chicago’)


#5

Wow. It really shocks me that you can’t set a time zone globally for a base, or for a user, etc. This seems to be a major oversight. Would really love this to get fixed asap.


#6

Is it possible to store all date times in UTC but anytime someone reads from or writes to them they do so in their local timezone? Isn’t this the best practice for all database schema design?


#7

Did we hear anything on this? 2018 :slight_smile:


#8

This isn’t working for me, either. Did you ever find a resolution?

EDIT: Apparently this does work if you wrap it in a DATE_FORMAT function. I can’t get it to display correctly otherwise.


#10

I am having the same problem. Cannot get it to display the correct time of creation.


#11

Similar issue coupled with treatment of date/time/timezone in a filter.

I am in EDT time zone. I used this formula to make the field show ‘today’s’ date in my own time zone (I also tried this with CREATED_TIME() rather than TODAY() with the same result):

DATETIME_FORMAT(SET_TIMEZONE(TODAY(),‘America/New_York’),‘MM/DD/YY’)

It does display the correct date in that column, however, now the field is not recognized as a date for formatting and I can’t use it as a filter criteria unless I want to type in the date I want to see. To goal is to create a view that will always show Today’s records for employees.

The same thing happens if I use a DATESTR formula to make the output a date string in another field and try to filter based on the new field. The output is not recognized as a date.

Additionally, if I set the field type to Created Time and leave the “Use same time zone for all collaborators (GMT)” OFF, then the field displays the correct date, but when I use that field as a filter, it shows results based on GMT times even though the filter actually specifies EDT.

For instance, if it is 4/21/18 and I add a record at 10pm EDT, the field will display 4/21/18, however, it filters based on GMT, making it +4 hours which makes it the following day. It does not show up if my filter is set to “Today”.

If I set the filter to ‘Tomorrow’, I will see my record that I entered at 10pm on 4/21/18 and the field actually displays 4/21/18.

A DATETIME_FORMAT or DATESTR result should be treated as a date, and the displayed date should override underlying date/time information. If that were the case, then the solutions above would be acceptable until a more permanent fix to the time zone issue is implemented.


#12

SOLVED:

Create a field with Created Time data type. Turn on the “Use same time zone for all collaborators” option to guard against future updates that change how dates/times are treated.

Create another field with a DATEADD formula to adjust the Created Time field to the correct time (and therefore date). Formula looks like this:

DATEADD({Created Time},-4,‘hours’) <-- This is subtracting 4 hours from the Created Time field because I am 4 hours behind GMT, which is the time zone the Created Time field is based on.

The result is treated like a date as it should be and the correct date is used for calcs and filtering.

To keep things easier to navigate when applying filters and making fixes and such, I named the original Created Time field to “DO NOT USE - Date Calc Field Only” and the formula field I will be using to filter on is now named “Date Created”.


#13

Hi

I too am having this issue with the current time.

I am quite surprised that Airtable does not have the time in real time.

I was wondering if someone can help with this issue.

The formula that I am using is:

DATETIME_FORMAT({DATE ISSUED},‘ddd, MMMM D, YYYY’)

Then I added this to the formula at the end:

DATETIME_FORMAT({DATE ISSUED},‘ddd, MMMM D, YYYY, h:mm A’)

All that happened is that the time was at 12:00 am, without changing.

My situation calls for a formula for date and current time in one field. I live in Vancouver, BC, Canada and I would need to adjust for Daylight Saving Time and Standard Time. Would it be possible, if one formula does not work for the time changes, (since the change date changes) to have two formulas for each of these time changes?

Any help would be appreciated.

Thank you,
MKr