Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Choosing the time zone used for Date and Time functions

Topic Labels: Dates & Timezones
Solved
Jump to Solution
50485 66
cancel
Showing results for 
Search instead for 
Did you mean: 
Chris_Rutledge
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

66 Replies 66

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”.

M_k
11 - Venus
11 - Venus

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

M_k
11 - Venus
11 - Venus

Hi

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

Thank you!
MK

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. :winking_face:


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.

M_k
11 - Venus
11 - Venus

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

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.

M_k
11 - Venus
11 - Venus

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

Trang_Le
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

I am surprised you don’t make it possible to set the timezone for a table. Defaulting to UTC is not really that helpful and for most users this will mean they have to either set up a seperate column to set the correct time zone or build of a data base that is set as the wrong timezone for them.

Secondly. It would be amazing if you could create a time column on its own.

I am sure many people have a ‘start time’ and and ‘end time’ and it is frustrating to have to enter a date for each.

Nora_Brown
6 - Interface Innovator
6 - Interface Innovator

Vote and comment on the product idea to set time zone per-base or account here: Easy way to change the local time zone