Handling multiple time zones

[Kirk_Masden]

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:

Time by server Questions

Consider… USERTZOFFSET() UTCNOW() (NOW() & " " & USERTZOFFSET())

How do I set a timestamp =now() to only be in a specific time zone Questions

My suggestion: consider converting all local times to UTC when storing (using an action), then converting UTC to local time for display (using a virtual column). The USERTZOFFSET() function returns the number of minutes the user’s local time is offset from UTC (though I don’t know if it includes a sign).

Time server? Questions

If I understand @moncet_idi concern, I have the same problem where a user can trick Appsheet into recording the wrong time by changing their device time. Does this mean that the UTCNOW() function posts the current time from say an independent server regardless of time set on the device ?

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 “AUTOCOMPUTE” 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:

Time server? Questions

If I understand @moncet_idi concern, I have the same problem where a user can trick Appsheet into recording the wrong time by changing their device time. Does this mean that the UTCNOW() function posts the current time from say an independent server regardless of time set on the device ?

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.

[Kirk_Masden]

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

How can I add minutes to a timestamp? If I'v... Questions

Hi again @Steve! After having tested your (UTCNOW() - NOW()) alternative for USERTZOFFSET() I would concur that (UTCNOW() - NOW()) works perfectly and that USERTZOFFSET() causes more problems than it solves because it renders the difference between the device’s time and UTC time in minutes, rather than a ready-to-compute format. In regard to the original topic of this thread (how to add minutes to a timestamp) I’d like to offer the following expression as a solution: concatenate( if([Minut…

[Anton_Rybak]

It should be not UTCNOW() - NOW() because this formula tells you the “position of the UTC” to user. If the user ahead of UTC, it will give you the negative number. But we need to know the user’s “position” to UTC. So, it should be NOW() - UTCNOW().
So, the formula [DateTime] + (NOW() - UTCNOW()) works perfect.

[Grant_Stead]

@Steve Can you write up the docs for USERTZOFFSET()

Also, does that handle DST?