šŸ•’ System/GMT Time - Solved!

This is an extension of the request for System Time, please.

Thereā€™s been a lot of questions for a while about how to get a universal time that can be shared for multiple users across multiple time zones. Most of you will know that any time your get a date/time from one of the glide special values, it will always be the userā€™s local time. But if you donā€™t know which time zone they are in, then you have no easy way to convert that date/time into something standard, like Greenwich Mean Time, to have a universal time thatā€™s the same for all users.

This thread (ā€˜3 days agoā€™, ā€˜a month agoā€™ ā€˜tomorrowā€™ ) started to get me thinking about this again. I had discovered quite awhile ago that you could you could enter a relative date/time (Fun with Dates), using words, into a date column and it would convert nicely into a date, so Iā€™m assuming that somewhere within Glide is a Javascript or Typescript library that does that conversion. I did some google searching to see if I could find a list of all of the different relative (word based) dates that could be plugged into a date column. What caught my eye was GMT.

Using that idea, I tried plugging in a date/time into a date column and add GMT to the end of that date. What it did was assume that the date/time I entered was actually GMT and it automatically converted it into my local date/time. Using that, you can then use some date math to determine your time zone offset and use that to convert a local time into GMT. I think this would be something great to put into a user profile table to automatically calculate the offset value, for each individual user, that could then be used throughout the app to convert a userā€™s entered local time into GMT and also convert it back to local time. (Actually just a template with a GMT time and the letters GMT, followed by a math column, would do that conversion.)

Here is a small app that can be copied and shares the concept:

image

(HOUR(gmt)-HOUR(ldt))

+

(24 * (
ABS((YEAR(gmt)*10000+MONTH(gmt)*100+DAY(gmt))
   -(YEAR(ldt)*10000+MONTH(ldt)*100+DAY(ldt)))
/   ((YEAR(gmt)*10000+MONTH(gmt)*100+DAY(gmt))
   -(YEAR(ldt)*10000+MONTH(ldt)*100+DAY(ldt)))
))

+

(MINUTE(gmt)-MINUTE(ldt))/60

@Mark @Jason I think the possibility is there for Glide to reasonably calculate this offset locally on the device without any server resources and do the calculations to determine GMT. Iā€™ve always suspected that the browser would be able to detect a userā€™s time zone and it appears that it can (at least Chrome). I also assume that this would automatically account for daylight savings time changes, but not necessarily for future or past dates when DST differs from when DST is active or not. That part may be tricky depending on how you use it.

Iā€™m curious to see if others get the correct offset and GMT times. From what I can tell, I think it should work really well. Please let me know if something isnā€™t right. I think my math is good, but itā€™s hard to say how it works around the world.

One bug I ran into is that creating the date with GMT template will allow it to convert to a date, and I can pick out pieces of it, such as year/month/day/hour/minute/second, but using it for basic date math just would not work (even when Math converting it to a date, it was still weird). I think behind the scenes itā€™s seen as a date, but not quite. Kind of hard to explain, but I really fought with the math while trying to account for the hour of the day crossing different days. I was trying to do a simple date subtraction to get the number of days different, but it just wouldnā€™t do it. Also the formatting in the math column would only follow the column type from the previous selection. So, in the dropdown, if I picked a number column for the math replacement value, then my GMT date column, it would only give me the Precision rounding option. If I first picked a date column, then my GMT date column, then I would get the date formatting options. Really weird, and I feel like Iā€™m exploiting a bug a little bit to make this workā€¦kindaā€¦but not really. Itā€™s almost like my GMT date column doesnā€™t really have a type, although it sort of acts like a date column. Anyway the offset math was a little more gross than it should have been, but still not too bad. I still figured out an alternative that seems to work well.

25 Likes

It got my local time correct, but the UK seems to have slipped into a time machine :grin:

3 Likes

Crap. I wonder if 24 vs 12 hour time or the date line is messing me up. What is your expected offset?

Iā€™m GMT+8

Iā€™m juggling a bunch of things at the moment, so I only managed to do that quick test. Iā€™ll definitely copy it and have a look under the hood when I get a chance, but that probably wonā€™t be until tomorrow.

1 Like

I have another workaround and works fine according to my tests.

In my solution I set/write the GS timestamp (timezone) in the sheet and later with math columns in GDE calculate the offset and the new Glide timestamp based on GS timezone.

So every 10 secs the new Glide timestamp (column) is updated and can be used without problems.

My problem is that Iā€™m out my home to see/test this and other solutions but as soon as I arrive I will write and compare ideas. At least, we are closer to solve this annoying topic.

Saludos

1 Like

OK, I made a change that may fix this. It was a quick fix, so Iā€™ll have to test more thoroughly, but seems better now.

1 Like

@gvalero Iā€™ve seen your other posts about this. This solution is entirely within glide and in real time, so hopefully it eliminates google sheets or any third party integrations.

Is the link in your original post now the updated version?

yes itā€™s the same link. Should just need a refresh.

yep, looks better now. :+1:

1 Like

Anyway, thanks Jeff - this is great and my mind is already in overdrive thinking about all the apps I want to apply it to :joy:

Butā€¦ Iā€™m not sure if this would actually help with the race condition that @Mark_Turrell and I have recently been dealing with. My gut tells me probably not, but I need to study it a bit first.

1 Like

BrexTime !

1 Like

Yeah, might be tricky if you are trying to calculate it on the fly, but if you have it calculated in the user profile, then you could readily pull it whenever you need it and could possibly avoid any race conditions.

image

1 Like

Congratsā€¦ that is a great solution.

Iā€™m most happy people have stopped talking about Zulu time and are giving Greenwich the respect it deserves (I expect nobody from Paris will agree!)ā€¦

2 Likes

OK, I think there is still a bug. I think it will completely freak out when the month rolls over to a new month. Iā€™ll have to take a look at that and fix it.

mmm, in our case the issue isnā€™t so much not knowing each users relative time, but more about dealing with the fact that we can have multiple users all trying to do the same thing at the same time, and because of sync delays they end up tripping over each other.

For example, record X becomes up for grabs, and 3 users all try to claim it at the same time. Because the initial change is local to each users device, it appears to each one that theyā€™ve claimed X, and so they move on to the next step. But then everything eventually syncs up with Glide and only one of them ends up with X (which usually seems to be the last one in - which I guess makes sense), and so the other two get booted out and wonder what the hell is going on.

The way weā€™ve ā€œsolvedā€ it is by deliberately introducing a delay, and a 3rd party to act as an arbitrator.
In this case the 3rd party is Integromat, but it could have just as easily been a bit of Apps Script. So what happens is:

  • Users A, B & C all try and claim X at about the same time by tapping a button
  • That button sends a webhook to Integromat, and our users are put in a holding pattern whilst we determine a ā€œwinnerā€
  • Integromat writes two values back to the Users GSheet for each user: X, and a UTC timestamp (NOW())
  • From another table, we build a relation to X, and then we do a rollup through that relation and take the earliest UTC timestamp
  • That timestamp is then combined with X in a template, and that template is used to build another relation back to the Users sheet.
  • Because only one of our users will have that exact timestamp, the relation will only be not empty for one user - and that user becomes our ā€œwinnerā€

So now Iā€™m wondering whether or not your solution could be applied to the above scenario - what do you think? :thinking:

1 Like

Iā€™ll ponder thatā€¦and probably reread it a few times. Someone else asked about that same situation here (Help with Users Overriding Each Other - #11 by Jeff_Hager), but none of my solutions were really that great. If the only thing Integromat is doing is assigning a UTC time, then I would think that step could be eliminated and you still go with the winner. The delay could possibly be introduced by some date math. But Iā€™ll think about it some more.

1 Like

My thinking is that pulling the time stamp from an external source is the key here. Because if the time stamp is applied immediately to the users local device, then weā€™re more or less back to square one, as each one will think that theyā€™ve won the race. But as always, Iā€™d love to be proven wrong on this :slightly_smiling_face:

1 Like

I think I have this fixed now.

2 Likes