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


#14

Hi

I was hoping that someone might be able to help me with this formula request in Reply #12.

Thank you!
MK


#15

I started a reply when you first posted, describing how various combinations of date configurations and display formats worked together, but I underestimated how much effort it would take to document, so I set it aside and haven’t had a chance to return to it.

One reason it was going to take so long to assemble is that I have yet to figure out Airtable’s handling of dates and times: To me, it’s one of those perfectly logical things that makes no sense. However, in working on this reply, I think I’ve discovered why I had so much trouble figuring things out: I was confusing field configuration with field formatting, which had me chasing my tail trying to understand why dates and times seemingly never wanted to sit still. As time permits, I’m going to try to put together a more-detailed discussion of date and time handling — but in the meantime…

In the example you gave in reply 12, you’ve left out an important part of the date and time formatting routine: Setting the appropriate timezone. Your formula should read

DATETIME_FORMAT(
    SET_TIMEZONE(
        {DATE ISSUED},
        [Your Timezone]
        )
    ‘ddd, MMMM D, YYYY, h:mm A’
    )

Where '[Your Timezone]' appears in the formula, you should insert the appropriate timezone identifier.

Now, will this give you what you want? It depends – mainly on how {Date Issued} is configured. If it is set not to use GMT, then the formula should return the value of {Date} in the format specified. If it is set to use GMT, though, the date and time indicated will be shifted by however many timezones you are currently offset from GMT.

As far as the earlier version of your formula returning a time field of 12:00 a.m., that suggests one of several possible situations.

  1. The date and time were set using the TODAY() function rather than NOW(); the former returns the current day with a timezone of midnight, while the latter returns the date and time stamp at the moment the formula was evaluated.

  2. The date and time field is configured not to include a time field. Such fields, when queried as to their time stamp, return midnight.

  3. The date and time field may have originally been configured to support a time field, but at some point the field was reconfigured without a time field. Should it later be re-reconfigured once again to support a time field, the field will have lost all time data and will return, once again, midnight.

    It is this last situation I describe as having confused field configuration with field formatting. At first glance it feels like a bug: Configure a date field with a time field, populate it by bringing up the date picker and clicking the 'Today' button, change the format to drop the time field, change the format again to restore the time field — and, suddenly, the time field reports '12:00 a.m.' Somehow, changing the field formatting alters the data… .

    Ah, but we didn’t change the field formatting: We reconfigured the field. Rather than altering the display format, we made a fundamental change to the underlying data structure. The proper analogy isn’t along the lines of adding another degree of precision, it’s more akin to changing a number to a single-line text. As such, the loss of time data should come as no surprise.

So, to recap:

  • The only way to be absolutely certain how a datetime value will be presented is to use SET_TIMEZONE(). (Note that SET_TIMEZONE() can only be used as the first parameter to DATETIME_FORMAT() — or, rather, that is the only place it can be used predictably.)

  • Date values are assumed to be in local time unless the ‘use GMT’ toggle is selected. Needless to say, your date values should either all use GMT or all use local time; mixing the two will only end in tears.

  • Date values without an explicit time field will return midnight (12:00 a.m.) on the specified date. Keep in mind, though, depending on where you are in relation to GMT, toggling GMT on can have the effect of shifting the date value back a day.

    For instance, consider two fields: The first is a date field; the second is a formula field with the formula cinfigured as {Date} — that is, it takes on the value of the date field. Initially, both are configured without a time field and to use local (not GMT) time. A value is entered into the date field, and the formula field mirrors it, both displaying the same date. Now, reconfigure the date field to select ‘use GMT.’ If the local timezone lies to the west of Greenwich, this will have the effect of shifting the value of the formula field earlier by as many hours as the local timezone if offset from GMT. Since the unshifted time field was set to midnight, or '00:00' on a 24-hour clock, any movement towards an earlier time pulls the value to the previous day. The formula field is now off by a day from the value of the date field.

I think the answer to your question is in there, somewhere. Me? I’m about to shift myself to bed; once there I intend to shift myself forward 4 or 5 hours, at least, without thought of Airtable. :wink:


Edit: The square brackets ([ / ]) in the example formula are just to indicate where a location-specific timezone specifier needs to be added; the brackets themselves should not be included in the final formula.


#16

Hi

Thank you for W_Vann_Hall for your reply.

I wondered if I could use a formula for the time only, in a separate field, different from the date field.

The Field that I created is:

TIME ADDED

but if that does not work out I created another Field called:

DATE AND TIME CREATED

I would like to use Local time not GMT (see, I pay attention) :grinning: for Vancouver, BC, Canada, my understanding is there is a Vancouver in the US, so I am differentiating between the two cities.

This is how the finished formula, that you provided, looks like:

DATETIME_FORMAT(
SET_TIMEZONE(
{DATE ISSUED},
[‘America/Vancouver’,]
)
‘ddd, MMMM D, YYYY, h:mm A’

I keep getting an error message and I set it up in the same Field (that I had used for the previous formula, after clearing it first), beside DATE ISSUED, and then I copied and pasted the formula into the formula field.

I was very interested in trying out this shiny new formula, that you were kind enough to provide, for a spin, but I got a flat tire :slightly_frowning_face:. Sometimes I think I am on the ball, but in the case of this formula, I am under the ball. This is my sense of humour, when formulas don’t work out.

Feeling deflated,
MK


#17

My bad: I use square brackets ([ and ]) to show where someone needs to add their own information to a formula — and I should make it clearer that the brackets are not to be included. Delete them — and make sure there’s a closing parenthesis after your format specifier (that is, close the entire formula with a right paren) — and your formula should work.


#18

Hi

Is there a way to create a formula for time only, local time zone, Vancouver, BC, Canada? I would need the current time to show.

I also have a formula for a date format only, would that interfere with the above formula request, placed in the same base, in a different field?

I did a bit of research of formulas, for Airtable. This is what I found and would this work? Do I specify the time zone? Where do I type the name of the field? Is it in place of “date/timestamp” words or (Invalid date)?

TIMESTR()

Thank you,
Mary


#19

Thanks everyone for the help. I’ve been wondering why a simple formula like adding 1 hour to a specific time results in a wrong value. I’m loving Airtable, but I’m shocked at this major oversight.