USERTZOFFSET() not working with datetime

AppSheet AppSheet Q&A
,
1

Hey appsheet fam - I’m importing data from a 3rd party that formats datetime thusly:

2022-01-05T20:55:01Z

Appsheet has no trouble reading this, but the dates and times passed in are UTC. When attempting to use USERTZOFFSET() to adjust the datetime, my function is subtracting 300 (my 5 hour offset) DAYS instead of minutes from the datetime.

I can get this working for TIME() values, but that’s not very helpful since certain entries will cross the date line.

How can USERTZOFFSET() be used to effect the date and time?

2

I didn’t know there was a “USERTZOFFSET()” inside AppSheet, there is no documentation about it, it’s just mentioned on the UTCNOW() article.

UTCNOW() | AppSheet Help Center

Now, why you need this USERTZOFFSET()? Can you share your expression?

Did you tried just by using:

DATETIME([TheColumn])-"00.05:00:00"
2 Likes
3

@SkrOYC “005:00:00” :slightly_smiling_face:

4

I meant “00.05:00:00”, although it’s the same thing as “005:00:00” in this context where there are no days added.

I actually stick with days.hours:minutes:seconds, I learnt this from your experiments mentioned on this great post:

The notions of TIME and DURATION and how to use in… - Google Cloud Community

1 Like
5

Thanks - while hardcoding the offset does work, it’s not as useful since we have a team of users distributed through many timezones. (Wouldn’t this break for several months a year during DST as well?)

6

A summary of USERTZOFFSET() is available here: https://www.googlecloudcommunity.com/gc/Tips-Tricks/Handling-multiple-time-zones/m-p/340189

1 Like