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