Convert Read Only data to User Setting Defined Time Zone / UTC converion

AppSheet AppSheet Q&A
, , ,
1

I have a large database of events ALL UTC - the app is read only. Simply want to be able to have the user in setting define their UTC offset and have ALL date / Time converted in APP. Reflecting TZ conversion.

Appreciate any help, have spent a few hours to not get anywhere am sure this is a simple workflow.

Cheers

Screen Shot 2022-05-29 at 6.26.25 am.png
Screen Shot 2022-05-29 at 6.26.25 am.png793Ă—1027 21.7 KB

2

Welcome to the community!

TZ shift can be dynamically calculated per user using this expression: NOW() - UTCNOW()

1 Like
3

Hi Joseph, thanks so much for the response. Where exactly do I put this?

4

Everywhere you intended to put the value of your “Time Zone Conversion” column. This expression will give you the TZ shift of the user without the need to ask him.

1 Like
5

Hi Jospeh, Thanks to take the time to help. Every small function has taken a lot of time to discover as I’m not used to conditional style code or expression. A couple of slices took me half a day.. ;)…

In the case that I want the user to choose the TZ. not have it automatic by App / location. there are cases where the UTC default is required. So it’s more mandatory to chose in settings then all events are converted. This will only ever be a read only app.

Thanks again for any more detail / light you can shed

6

No problem at all. I’m just having difficulty figuring out where exactly you need help. You already seem to be able to collect the selected user’s time zone through the enum column you show in your post. So where exactly are you having difficulty please?

1 Like
7

Yep Have set up the Settings enum choice of TZ. But I don’t know how to implement that choice to convert All events throughout onto all tables. The dates and time of events is one of the most important. As the database is simply a big list of events.

8

I see, thank you.

User settings can be retrieved using the USERSETTINGS(“columnName”) expression. Here, you make your users choose among text values, which wouldn’t be suitable for dateTime calculations. You need the number after “UTC” and you need to convert that number to a Duration value so that you can subtract it from the dateTime columns associated to your events.

Please also notes that there exist more timezones than the ones in your column. There are TZs with :30 and :45 shifts.

What you can do is to user another table, let’s calle it tzShifts, with two columns:

  1. One Key column of type Duration with the values listed in rows: “000:00:00”, “001:00:00”, “002:00:00”, and “-001:00:00”, “-002:00:00”, etc.
  2. One Label column of type Text, with the following corresponding values: “UTC”, “UTC+1”, “UTC+2”, and “UTC-1”, “UTC-2”, etc.

Now in Settings, change the “Time Zone Conversion” column type to Ref pointing towards the tzShifts table, with a Duration base type. With this, in the Settings form, the user will see UTCs but will choose the corresponding durations instead.

Then when you need to calculate the local DateTime of an event based on the Time Zone a user has selected, you can do:

[eventDateTime] - USERSETTINGS(“Time Zone Conversion”)

Please read the following resources before implementing the solution:

2 Likes
9

Ok, great ! - going to give this a go. Thanks !!

2 Likes