Timezone not captured in Date Time component

The Date Time picker captures a ISO 8601 timestamp but the time zone is always set to Z.

Example: 2020-04-06T16:00:00.000Z

This means the time zone is not captured in the user’s local time and therefore the calendar display is not correct for users in different timezones.

Is this a bug or did I miss a setting somewhere?

It’s not a bug. You need to do the conversion in your sheet to whatever timezone you want.

Since the user’s time zone is not captured by the picker, there’s nothing to convert. It is always set to Z.

Is there an example of a Glide app anywhere showing proper time zone capture?

Within the Glide app the timezone is taken from the phone settings so it should sync up with the proper timezone.
If the entry is specific for that user then you are correct, you would need to ask for the users timezone as part of their profile and then use that in the Google sheet change the time and then create a text version of it to feed back to the app in it’s own column, maybe called Logged In Users time. If you want all the times represented as the same timezone as the sheet you would do a similar thing in the sheet but apply the sheets timezone setting to all the dates.

@George_B this may or may not be related to a similar hurdle I’m trying to jump. Perhaps you can help. I have users posting content. I’m having Glide send Current Date/Time as a special value with each post that a user makes. But folks are all over the place. I’m in Los Angeles. I have people posting stuff with dates that are two-three hours later than what is relative to my sheet’s ()Now function. This is giving me some trouble since, I’m trying to calculate a Streak formula in sheets, and I’m using ()Now for that formula. People in timezone ahead of mine would get more hours of a day without breaking their streak because my Now() function would take hours to catch up to when the sheet says they actually posted. Folks in time zones behind me (like folks in Hawaii) would have less hours in their day. Asking users to choose a timezone in their profile would be a pretty weak solution to this since any savvy users would know to just fake their timezone to the furthest ahead as possible (somewhere out in Oceana). Is there really no way for Glide to send the same time zone for everyone and you switch it yourself in Sheets?

Could you initially have the user create a profile record, which will submit their current local datetime to the sheet? Then run a script which will write the sheets datetime or UTC datetime to a different column. Then calculate the hour difference between the two datetimes. This would automatically give you the users time zone difference without requiring them to enter it and you that value anywhere in your sheet calculations.

Yeah @Jeff_Hager , I thought I could do that when a user signs into the app for the first time, since all users are automatically given a profile in the app based on the data in App: Logins which includes a login date. However, looking at some of those dates, including one from a buddy who logged in yesterday from Australia, I can say with pretty near certainty that those dates in App: Logins are UTC. So in that case, I don’t have any information on where users signing in are coming from. Adding a profile record and a script seems like a lot of extra steps and I guess I just wonder… if App: Logins timestamps are in UTC, why are dates sent from the Current Date/Time component in a users local timezone? Would be so much more consistent and less helper columns and scripts if all dates were sent in UTC. @Mark can you shed any light on this? Why the two different timestamp types? Can we expect a future feature that allow us to choose the timezone that Current Date/Time sends?

I suppose in the interim, I’ll do what you said. I actually have a Terms of Service agreement that sends current date/time from Glide. I suppose I can write the script and add an extra column for UTC comparison. Ugh… so many new columns! :wink:

Thanks @Jeff_Hager

Also, keep in mind, the fix you describe really only works if the user is posting from the timezone they first signed up with. If that person goes on a trip and posts from elsewhere, or they just straight-up move, the original information captured for them doesn’t mean anything to the underlying code anymore. For social content platforms, consistent dating across all content is important even just for sorting purposes.

UTC for Current Date/Time… pretty please, devs?

We know we have to solve the time zone problem, but unfortunately it’s not as trivial as adjusting everything to the local time zone.

An example: Let’s say the spreadsheet’s time zone is GMT, to make things easy. You submit a form at 7am in San Francisco, so it’ll show up as 2pm in the spreadsheet (SF is at GMT-7). Glide now obviously has to adjust date/times in the sheet back to your timezone, to make the 2pm show up as 7am on your device. Somebody else at GMT builds an app for people attending a conference. The conference takes place in Vienna and the keynote is at 9am. If they put in 9am, somebody in Vienna will see it as 10am, and somebody in SF will see it as 2am, when actually everybody should see 9am, regardless of where they are.

It seems to me that eventually app creators will have to tell Glide which date/times are to be adjusted to local time zones, and which ones are “absolute”. For the time being we chose what we thought would be what causes the least net confusion.

The “App: Logins” sheet was only intended for informational purposes when we introduced it, and we didn’t much consider how its date/times mesh with the ones in the data sheets.

So, apologies for all the trouble. We’re thinking about the problem.

2 Likes

Your current approach would be useful even though its imperfect. However Glide is inconsistent across components. From my testing, here’s what I see:

  • Date time picker = time zone is always GMT independent of the user or spreadsheet sheeting
  • Current date/time special value = time zone is users browser settings
  • Calendar layout = display is converted to users browser setting sometimes, dependent on formats used in the spreadsheet’s date/time cells. Haven’t debugged what causes this yet.
2 Likes

Knowing that you guys are thinking about the problem gives me great confidence it will be solved when the time is right. Thanks for insight!

1 Like

@cdury I’ll look into that. Thanks!

The calendar layouts for tabs and inline lists are not working still. The Date Time is not displayed in the user’s browser time zone on mobile devices.

To test it, I created an app with the sheet in GMT and Date Time entries are 2020-04-04T22:10:00.000Z.

Try it yourself: borgy.glideapp.io

Let me know if it works for you and any ideas for our to make it display properly.

I know the timestamp shows as zulu time, but I think it’s really just a datetime where timezone is irrelevant. Initially the date time was not stored in an ISO format and that caused all kinds of issues for people that did not have a US local. I know back then I had suggested an ISO format. What I meant was more of a YYYY-MM-DD format (not necessarily true ISO). YYYYMMDD is internationally recognized irregardless of regional local. I think that’s why Glide switched over to a true ISO format. It’s a standard layout in YYYY-MM-DD that works better for users worldwide. I don’t think the intention was to honor timezone. Just to make a standard datetime that works better.

I don’t think the calendar is designed to show times based on timezone. Irrelevant if it shows Zulu or not, the hour and minute in the timestamp is the hour and minute that shows in the calendar. Same with the date/time picker or date/time special value. The date and time you type in a spreadsheet is the date and time that shows in the calendar and date/time picker. When using the date/time picker, the date/time you pick is the date/time that’s stored. When submitting data that includes the date/time special value, the date/time is that of the user’s device. It’s a pretty straight forward what-you-see-is-what-you-get. Time zone has no bearing on any of this.

From what I’m understanding, the glide generated ‘app:***’ sheets are server generated and using a server time which may be set to GMT.

I think overall, it’s best to assume that any user generated date/times are not converting anything to GMT. It’s just the time that the value was generated on that user’s device or by that user.

Correct me if I’m wrong, but I’ve never entered a date/time on either the sheet or in the app and seen a different value stored to account for a timezone. Maybe I just haven’t run into it, but I feel like the ‘Z’ in the ISO date/time is irrelevant.

I’ve tried to be clear about the problems, but it seems I’m failing. There are two sets of problems: capture and display.

@Jeff_Hager You described the current timestamp capture behavior. Lots to be desired because it is inconsistent but I understand how it works.

@Mark You described the display behavior in the schedule components. But they do not work as described.

As it stands, I don’t see how the schedule components are useful for anyone that has users in more than one timezone. I thought that maybe there is a workaround that does both capture and display properly, which why I opened this thread, but instead we’ve had a discussion of theory.

Have you seen an app the behaves the way you described for both capture and display in more than one timezone?

Yes, I understand what you are saying.

If people from different time zones are physically traveling to and attending a meeting at a single location, then the time displayed in the calendar is relative and correct for all users.

If people from different time zones are attending a virtual meeting and staying within their own time zone, then the time of the meeting will not be correct for all participants.

I understand the problem and you can go either way either way depending on the situation. The calendar is a pretty basic component as it stands, but I think it would require either the app developer or the user to specify or allow preferences for each user if they want timezone to apply or not. This would be different depending on the situation. Right now you would have to have the user specify the timezone and perform the math in the sheet to change an entered datetime to GMT. Then perform the math again for each user.

You have a valid argument, but as it stands, it’s not as robust as Outlook or Google Calendar. I wouldn’t say it’s inconsistent, but moreso it’s consistent in how it works right now with the time being represented as the same value for all users irregardless of the timezone.

It might be worth a feature request to be able to apply or not apply timezone functionality to a date column. https://features.staging.glideapp.io/

I just got around to working on this, and I can’t reproduce this. I made an Add screen that would write one column via a date/time pick, and another via the special value. They both end up in the same format in the spreadsheet. I also used a Calendar on both columns, and I get the correct date/time.

If you still see this issue, could you please make a video?

From my tests it seems there is a bug in the timezone conversion:

For example:
I set an event for 10AM in calendar picker.
It appears in Google Sheets as 10AM ZULU.
I see it in my phone (with GMT timezone in settings) as 10AM.
I change my phone settings timezone to EST (i.e. ZULU-5). I see the event in my phone as 15H. The correct time should be 5AM.

I did the same test with time zones in the other way (i.e. ZULU+5) and the same thing happens.

I can only conclude that there is a bug and that the conversion from ZULU timezone to users time zone is being done in the opposite way. Could you please clarify @Mark ?

Another question:
I will have users in several timezones organising online events. So each user can i) add events (the events should be added in their timezone) and ii) see events from other users (for this, the timezone of the events should be converted to their own timezone).

What is the simpler way to do this?
When the user is adding an event through date picker, ask the timezone, convert that for a new column in the google sheets (Date2).
Calendar will read this column (Date2) and then each user will be able to see the event in their own timezone since the calendar view timezone is automatically converted according to user settings?

Have you solved this? Can’t understand why timezone is captured from device settings sometimes and others it isn’t and didn’t had any feedback from glide team