@Jo-lCollins
I think maybe I wasn't clear enough that trying to report times or include specialized time zone offsets is a bad idea and not something that Canvas should do. I was trying to show that there are too many ways to represent time zones to try and cover them all. What you need for Redshift may not be what I need for MySQL or what someone else needs for MSSQL. Even saying "GMT-5" isn't universal as I know MySQL wants -05:00 or -5:00. There is a standard and that's what Canvas is giving. It's up to you to do the conversion.
Canvas records all times in UTC. Many calls from within Canvas are returned in local time (for the user making the request or the course time -- not sure which). But in the database itself, it's stored in UTC. That means that in Canvas Data 2, it's in UTC. If you want to convert it to local time, they provide the time zone for the user, course, and perhaps the institution (if not, you could hard-code that one), so that you can convert it to whatever time zone you want. You could use a COALESCE() function to get the most specific (user, then course, then institution).
Using UTC is the right choice as it avoids any ambiguities in time related to daylight saving time that would occur when times are stored locally.
With Canvas Data 2, the tables are normalized and having a table of just time zone conversions is rather straight-forward to create and once you have it, it likely won't change much. I gave you a site that had the conversions and it would not be hard to incorporate that into your request.
One downside is that people may change their time zones. I doubt that's a very big issue. There's a bigger issue that may give you incorrect data.
Knowing the time zone issue isn't necessarily going to give you what you want -- regardless of whether it's reported by Canvas or not. Not all users set their time zone, even when they're in a different zone. I'm in Illinois (US/Central) but had a student from Thailand during the COVID shutdown. She kept her on time zone for US/Central because it was easier for her. I had even made a video showing her how to change it in case she didn't know. She also had instructors who hard-coded times into their content, so that made it easier to match up.
Likewise, if I am traveling, I don't change my time zone in Canvas to match where I'm at, even if I'm there for a week. I may change the time zone on my laptop (I just remembered that I need to change it back after my recent trip to the east coast), but not usually in the applications I run.
I once analyzed when students were doing work. I used it to help set my office hours when we were online-only for the pandemic. I had weird office hours of 9 pm because that's when students were working on things. I rarely had anyone use those office hours, but they likely wouldn't have anyway. What I discovered was that most either did work right before they went to bed or (more commonly) right before it was due.
If you have Live Events data that include the time zone, I wouldn't worry about about not being guaranteed 100% coverage. It still suffers from the "I didn't tell Canvas my correct time zone" syndrome that Canvas Data 2 would. Nothing you're going to get out of this is going to be 100% correct because of that. Whether you get 100% coverage with bad data or 98% coverage with bad data, it's still bad data.
To back up the notion that you're not going to get completely accurate data, I asked Canvas Data 2 to tell me how many users we had with a time zone specified. There were 15885 with a time zone set and 21295 without a time zone set. That's only 43% of users who have their personal time zone set, which is why it's important to use the COALESCE() function to get some time zone if you want to do what you want to do. Then I asked it how many of those 15885 were not "Central Time (US & Canada)", which would be our default. There were 27 and I recognize at least 3 of those as service accounts from either Instructure or a third-party vendor. We have some online programs that draw from across the United States, so I know that we have had more than 24 users in the last 12 years from a different time zone.
Even having time zone information with Live Events doesn't guarantee anything. Not every Live Event returns the time zone. you may be lucky in that the one's you're looking for do, but I looked at our last full month of data. We're a small college and so I only had 1,391,686 events for May 2024. Of those, 383,837 events did not have a time zone. That means that about 72.4% of events did have a time zone. Since 43% of users haven't set their time zones but 72.4% of Live Event data include it, that suggests that Canvas is using the course time zone when the user doesn't have one. It may even be possible that it's only using the course time zone when reporting, I haven't dug into it enough to know -- but if that's the case, then you're only slightly better off.
It's even less reliable than you think. I asked Canvas Data 2 what the time zones for my users were. 15858 were from Central Time, 12 Eastern, 8 Pacific, 4 Mountain, 1 Hawaii, 1 Central America, and 1 Tashkent (Uzbekistan). Then I looked at the time zones reported from Live Events. I had 998071 from America/Chicago (Central), 9289 null, 200 from America/Los_Angeles (Pacific), 133 from America/Guatemala (Central America), 110 from America/Denver (Mountain), 44 from Africa/Harare (Central Africa), and 2 from Europe/Budapest (Central European).
Some things to note
- First, Canvas Data 2 and Live Events don't use the same time zone names. So if you hope to pull data from both, you're going to have to convert between them -- that's where the separate table comes in. Live Events at least give you a Redshift-supported name.
- Second, they don't match the time zones set for the users in Canvas. I didn't have anyone with a time zone from Africa or Central European, but those are showing up in the data.
- The time zones must be coming from the something about the user's device and not their settings in Canvas. I have 4792 courses with "Central Time (US & Canada)" listed and 219 with "America/Chicago" listed. There were also 5615 with no time zone set. None of the courses have any other time zone set. If it was based on the course, there could be no Africa/Harare time zone. No one had Central Africa selected as a time zone. I don't know if Canvas tries to figure that out from the IP address or if it sends back the time zone as a request that then gets added to the cookie for future calls, but it doesn't seem to original within a Canvas time zone setting.
- Because Canvas Data 2 isn't consistent, that means that I need to be able to handle both types of time zones. Strangely, none of the America/Chicago appeared before Fall 2016.
- Canvas Data 2 is less likely to actually represent where the student is than Live Events.
You might be able to do some kind of geo-IP lookup, but that's not reliable either, especially with increased VPN usage. A couple of years ago, I had a student who claimed he was at the local library, but his account was submitting work from Germany and Orlando while he was also showing activity from the library -- all within a few seconds of each other. I probably wouldn't have noticed if the assignment wasn't vastly superior to anything he had turned in all semester while not being anywhere close to what the assignment asked for.
It's possible that the locations came from Canvas doing a geo-lookup on the IP address. That could explain the Europe/Budapest -- someone using a VPN. But then that gives you absolutely no clue of what local time they were doing their work on.
If you happen to be a land-based institution where most of your students are from one time zone, it might be just as accurate to adjust everyone to that one time zone than to try to figure out where they really were. And you might find a stronger relationship with what time the assignment is due than what the local time was for the student.
