🕒 System/GMT Time - Solved!

Currently the flow’s like this:

Every 7am Mexico City time I run through all the rows in the Profiles sheet, then pickout only the ones that has the same “Reminder weekday” as the “Now” weekday of Integromat, then send them emails.

With your method here, I’m thinking about an extra offset column to let me calculate the “current” time of each user, in the Sheet. I would run the scenario every hour to check for rows that have the “calculated” time as 7am, for example.

So it comes from sending everyone emails at 7am Mexico City, to being able to send emails at 7am their time.

Yeah, I think that would work. Trying to run it through my head, but I’m sure there’s a lot of parts to it.
So basically you are figuring out the user’s current day. I fall into the same time as Mexico City. So for example, I assume you are 12 hours ahead. I get confused with the international date line, but as of right now you are a day ahead from me, but at 7:00am my time, we would still be within the same day. With that I wonder if you would have a problem at all. But ignoring that, lets say your process runs in the next half hour and you picked Thursday as your day. As it stands, it would not pick up your day because Mexico is still on Wednesday. So if you had your offset of +7 hours combined with the -5 for Mexico, you would have a +12 hour offset, so you would need to add 12 hours to Mexico local time and then check the weekday of that calculated date. In theory, I think it should work out pretty well.

Some timezones have daylight savings so the time shifts by an hour, so I’m not sure if that would cause some rare situations where the calculated date would jump by an extra hour, but hopefully that wouldn’t be much of an issue.

3 Likes

Thanks for thinking through Jeff! Another great method from our Chuck Norris!

1 Like

Finally had a chance to study this. Absolutely brilliant.

1 Like

LOVE IT! I’ll have to set aside some time to test this in one of my apps.

1 Like

@Jeff_Hager, here is my solution for this issue although I think, both solutions complement each other.

In my case, I have a Google Sheet with a timezone XX and I try to have on APP the time based on that GS timezone and not the user timezone which is used by Glide.

If my GS is using “Europe/Berlin” timezone (GMT +01) and my APP has multizone users around the world (USA, Brazil, Spain or Venezuela) buying tickets or making a booking I’m going to need to have transactions timestamp based on my GS (Berlin) always to have a real order and know the sales queue correctly (based on time).
Otherwise, If I use the Glide time (based on device/user timezone) my transaction log will be hard to understand and sort out.

The key in my case is write and use the GS timezone into a cell using the Now() function. This will rule everything regarding time and date. It’s the only thing you will need to use in your GS.

image … . image

Later, in GDE you will have these 3 new columns with a simple logic/math:

  1. Glide Time (Local Time)
    image

  2. Time Offset

  3. new Glide Time (based on GS Timezone)

And trying to replicate your APP demo :innocent: , you can see what happens on my APP verifying several timezones and times:

https://glide-time.glideapp.io

In my case, I’m in Venezuela and have -6 hr with Berlin and @Mark_Turrell’s house :wink:.
With it, any transaction done by me (or any user) from APP will be logged using Berlin’s time instead of local time (user timezone).

Although few people and in few times the GS’ timezone will be changed frequently, I created a choice list to carry out it automatically and test better my solution (using a small script as well). None of you needs to have it on your APP to get this functionality, this is not mandatory.
Again, I created the timezone choice list and my script to make this APP demo more friendly only.

I hope it helps anyone.

Saludos!.

6 Likes

yeah that’s essentially what @Simon_Hill did:

It’s a nice simple solution for determining users timezones, but again I don’t believe it helps with the “Great Agent Race”

1 Like

@Jeff_Hager

One word sums up my thoughts about your solution - ‘Genius’

I just want to say the value you bring to the community is absolutely amazing.

I hope you realise how much we appreciate your insight and wisdom and the positive impact your solutions play in building our apps.

Here is a virtual :beer: or :coffee:

6 Likes

Hola @Darren_Murphy

Yes, it looks like the @Simon_Hill’s idea but there is one difference:

Simon needs to send any value previously to his GS to update the time and later use it in any operation. If your APP has a Pro plan, it isn’t mandatory because of Glide will update APP from GS each 4-5 min (including everything associated to Now() function).
But even using a Pro plan, Simon still needs to send a value to GS (via an Increment action) to avoid losing up to 4-5 min caused by Glide’s automatic update.
My solution doesn’t have this problem with a Pro Plan. Once APP has read the time from cell, the new Glide time is updated every 10 sec by Glide internally and automatically.

Instead, if your APP uses a free plan, you will need to do something like what Simon does but there is a time window about 1 hr in where you still have the synchronized time without problems.

Later I will go over your crazy “agents race” :upside_down_face:

Saludos amigos!

3 Likes

Just to clarify, whatever your plan so long as you change something in the GS then the entire sheet will be updated instantly. I don’t use ‘increment’ anymore. Instead I use ‘set column’ and push the current Date/time into any redundant cell. It’s pretty easy to add these ‘trip wires’ into any app so that common user actions automatically trigger a GS time update.

1 Like

That’s a good solution too. I was going to ask about how the sheet time was updated in glide, but now that makes sense with the background refresh, or setting a value to resync. So in theory, you should never have a time drift of more than 5 minutes, so your rounded offset calculation will always fall to the nearest hour.

Just something to be aware of, there are those rare timezones that differ by an additional 30 or 45 minutes. May not matter much depending on the scope of your app, or where the user’s are primarily located, but something to consider.

1 Like

Thank you! I’ll take both. :wink:

Glad to help. Some of this stuff is fun to try to figure out.

1 Like

Hola de nuevo

@Simon_Hill no problem, I understand what you meant. What we are looking for (in my case) is a reliable time using an automatic way without firing a manual action in the middle. Also, if we can do it using Glide tools 100% we would make a home run but sometimes we wish and sometimes we fail :upside_down_face:

@Jeff_Hager you’re absolutely right… te odio! :rofl:
You remind me my mom, she always paid attention to details and those rare scenarios that make an exception. I had noticed that problem with timezones with -/+ 30 or 45 min thinking in my country (currently Venezuela quit using -4:30 offset and now we use 4:00).
Having said this, my workaround is useful for 93% of cases :zipper_mouth_face:

Have a nice night… feliz noche!

1 Like

Just to clarify, my solution is 100% Glide. 4 columns in a glide table to determine GMT, which could probably be condensed into 2 columns, to determine GMT or any other time zone (GMT+2 or GMT-2) if needed.

1 Like

@Jeff_Hager the medium format of date/time breaks the logic because it is localized and might include some words that are not “understood” by your logic (in my case it’s “tháng” - which means “month”).

A short format fixes it.

1 Like

Good to know. I’ll update it shortly. Looks like only the Local time needs to be in the short format?

1 Like

Yes, only the first one.

1 Like

Gvalero, to confirm:

My solution works 100% of the time so long as the app is active
Your solution works 93% of the time but still works when the app is not being used. :man_shrugging:

1 Like

I think he means 93% in that it covers all timezones except those rare ones that fall on the 1/2 hour or 3/4 hour, which could throw off the result by an hour for part of an hour. But only in those odd timezones.

1 Like

@ThinhDinh Ok, I changed the Local Date Time to the Short Format for the calculation and added a new math column so I could reformat it to the Medium Format for display purposes.

1 Like