Handling multiple time zones

The column type “ChangeTimeStamp” records the date and time a change occurred to another column according to the time zone of the device being used. In many situations this is probably just fine, but what if you need to know the sequence in which events actually occurred? A single user who flies to a different time zone may appear to be doing A after B, when A actually came first. Or, a similar problem will occur when there are multiple users in different time zones.

I have learned from expression guru @Steve that it is now possible to use the following expression to disentangle such knotty time issues:

USERTZOFFSET()

This function has been discussed in the following threads:

I am in Japan, which is 9 hours ahead UTC time. If I make an action to record USERTZOFFSET(), I get “-540”, which tells me that I need to subtract 540 minutes from the time in Japan in order to get UTC time.

So, if one combines a normal “ChangeTimeStamp” with an action that records the USERTZOFFSET(), the UTC of the timestamp can be calculated. Unfortunately, however, the need to use a separate action to record the time zone of the user and then recalculate the time makes app building more complicated and may lead to slower sync times. If only we could set the “ChangeTimeStamp” to UTC?

But wait, I think there’s a hack for that!

I took a normal “ChangeTimeStamp” column and placed a “UTCNOW()” expression in the “AUTOCOMPUTER” app formula spot. This caused the column type to be changed automatically from ChangeTimeStamp to DateTime:

But wonder of wonder, miracles of miracles . . . it continues to function as a ChangeTimeStamp column . . . and the times recorded are now in UTC!

Moreover, the UTC times are those of my device, not the AppSheet server. I confirmed this by setting my device (a clunky Huawei phone) to Airplane mode, using it for a while, and then returning to the internet to sync. This contradicts earlier discussion of UTCNOW(), which has been said to be recorded as the time on the AppSheet server:

I’m not trying to say that the previous discussion is wrong in general – only that I got a different result in this particular situation.

So, that’s the hack. But it’s a hack that I’m not completely comfortable with because I’m worried that it may not be stable. In other words, it works but there’s nothing in the AppSheet interface that assures me that it will continue to work. So, I would like to request that AppSheet add a UTC option to its ChangeTimeStamp column type, please.

2 Likes

Addendum: In my post, I wrote that USERTZOFFSET() could be used to record the time zone of the device. Unfortunately, however, the result (for example, “-540” in the case of Japan) is not in a format that is ready to be added to or subtracted from another time in your app. I’d like to thank @Steve for pointing out that UTCNOW() - NOW() is an alternative to USERTZOFFSET() that produces a readily computable result. Also, though there has been some discussion indicated that UTCNOW yields the UTC time on the server (that is, the time that data is written to the server, not the time that it occurred on the device), that does not seem to be the case after all so I think you can use UTCNOW() with confidence.

Finally, if you need to use the result of USERTZOFFSET() in a formula to adjust a time, you can convert it to the proper format with the following expression:

concatenate(
if([Minutes]>0,"","-"),
left(“00”,2-len(text(floor(abs([Minutes])/60)))),
floor(abs([Minutes])/60),
“:”,
left(“00”,2-len(text(mod(abs([Minutes]),60)))),
mod(abs([Minutes]),60),
“:00”
)