Choosing the time zone used for Date and Time functions

[Chris_Rutledge]

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?

[Jack_McTigue_UM]

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

[Alexander_Sorok]

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

[RobTX · Accepted solution]

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

[BenInDallas]

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.

[chris_calo]

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?

[Dharamveer_Sing]

Did we hear anything on this? 2018 :slightly_smiling_face:

[Cathy_Lewis]

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.

[Richard_Mast]

RobTX:

DATETIME_FORMAT(SET_TIMEZONE(CREATED_TIME(), ‘America/Chicago’), ‘M/DD/YYYY h:mm A’)

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

[Sasha_Wooldrid1]

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.