🕒 System/GMT Time - Solved!

[Jeff_Hager]

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:

System Date/GMT Concept

(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.

[Darren_Murphy]

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

[Jeff_Hager]

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

[Darren_Murphy]

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.

[gvalero]

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

[Jeff_Hager]

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.

[Jeff_Hager]

@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.

[Darren_Murphy]

@Jeff_Hager

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

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

[Jeff_Hager]

yes it’s the same link. Should just need a refresh.

[Darren_Murphy]

yep, looks better now.

[Darren_Murphy]

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

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.

[AyS_0908]

BrexTime !

[Jeff_Hager]

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.

[Lucas_Pires]

[Simon_Hill]

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!)…

[Jeff_Hager]

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.

[Darren_Murphy]

@Jeff_Hager

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.

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?

[Jeff_Hager]

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.

[Darren_Murphy]

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

[Jeff_Hager]

@Jeff_Hager

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.

I think I have this fixed now.