Help

Re: Why does TIMESTR() add 8 hours to result?

1194 1
cancel
Showing results for 
Search instead for 
Did you mean: 

Just noticed this.

I have a timestamp field (that is, a date field that also shows time). I want to extract just the time. The result of the TIMESTR() function seems to add 8 hours to the result. For example,

MyTimestamp = 01/27/2021 10:12am
TIMESTR( MyTimestamp ) = 16:12:32

What’s up with that? I’m in Texas, six hours behind GMT/UTC. (It’s 5:30pm here, 11:30pm in Greenwich.) So if the time zone offset from GMT were involved here, I’d expect the result to be 14:12:32.

William

4 Replies 4

It should be returning GMT Time Zone, so not sure what’s going on there.

However, you can force it to Central Time with this formula:

TIMESTR(SET_TIMEZONE({MyTimestamp},'America/Chicago'))

Thank you so much for your help. I’ve never used the SET_TIMEZONE() function but it does the trick perfectly!

But I have a follow-up question. Apparently the value in the source field, the value that TIMESTR() is formatting, is, under the hood, a GMT value. In other words, it’s basically an absolute. Now, your formula formats that absolute value in relative terms – CST in the US. What happens if this base is accessed by another user who is, say, in California (Pacific time zone)?

To put it differently: Why is TIMESTR() adjusting things at all? I’ve got a value in the source field that’s, say, 21:00 in GMT, but I’ve asked Airtable to adjust for the local time zone, so here in Texas I’m seeing 15:00 (GMT-6). If I use a string formula to extract a value from the source timestamp, why doesn’t that time zone adjustment carry over to the string result?

I looked at the list of values that are accepted as parameters or SET_TIMEZONE() and there doesn’t seem to be anything equivalent to “Local Timezone”. Am I just missing it?

Thanks again…

The only way to access the “local timezone” is to turn OFF the GMT setting, and then it pulls the local timezone based on your computer’s time zone settings.

I’m not really sure why TIMESTR() is changing the time zone of your source field, if your source field already has its GMT Time Zone setting turned on. On my end, I’m not seeing that behavior. On my end, if my source field is set to GMT, then the TIMESTR() function gives me the exact same time that I see in the source field. You may want to email support@airtable.com to ask them what’s going on with that.

Also, I created a whole training video & sample base which does a complete deep dive into Time Zones — this might be very beneficial for you, particularly if you’re working with other users in other time zones:

Wow, that link looks great. Thanks so much. I’ll check it out later today. And thanks again for your help. I bet I’m doing something wrong but I think you’ve given me enough to sort things out.