Canvas Data 2 - User Time Zone Code

[Jo-lCollins]

Hi Instructure!

Is there a way to access the time zone code for the user's set time zone?

Example:

1. Right now, a user can set their time zone in the LMS to  Eastern Time (US & Canada) (-06:00/-04:00)at /profile/settings.
2. The time zone value for this user via Canvas Data 2 looks like this: Eastern Time (US & Canada)

Problem

The current format, to my knowledge, requires that I manually map every possible time zone selection to the conversion code if I want to analyze user activity in their set time zone. 

Possible Solutions

To convert the time zone (I'm using redshift), I need either the offset which is being stripped, or one of the following code formats: [US/Eastern, EDT]

To me the solutions are either

1. Add a new column to the users table with the code or offset
2. Alter the format of existing time zone field in the users table so it includes the code or offset. If it is easier to include the offset via the format that appears in the LMS I can use regex to grab it.

Alternatively, let me know if ya'll know of a better solution! Thank you!

[robotcars]

Not a solution to your problem, but some considerations.

• If you store an extra column in a dap table, you may have issues with sync.
• If your users change timezones frequently storing 1 value might leave some records with the wrong offset.
• You might consider combining something like Live Events, to know the timezone of the user during a window.

[James]

@Jo-lCollins 

Building off what Robert ( @robotcars ) said, rather than adding the local time to the users table, I would create a separate table -- not one managed by Canvas Data 2-- that contains the conversion. Then you can join the data with that table to get the zone information that you need.

I would further discourage using the offset as they change based on whether it's daylight saving time or not. So the user isn't really setting their time zone to use Eastern Time (US & Canada) (-06:00/-04:00), that's what is being displayed to the user to help them understand what the time zone is. Different programs have different ways of representing the time zones.

In Moment JS, which Canvas uses for web-based time management, it's called "US/Eastern". In Ruby, which is the language the Canvas server uses, "Eastern Time (US & Canada)". The IANA calls it America/New_York (although there are other cities listed). Some call it EST or EDT, but that's confusing because it depends on whether daylight saving time is in effect, so it's a bad way to store it.

PostgreSQL, the database that Canvas runs is what Redshift is based on, so the time zone support should be similar. You can issue pg_timezone_names(); to get a list of supported names. I don't have PostgreSQL installed to test it, but it looks like US/Eastern, America/New_York, and EST/EDT are supported. I would avoid the EST/EDT approach.

Because there are so many ways that it could be stored, you're likely going to need to map it yourself. There is a mapping from Ruby on Rails to the IANA values provided by Ruby on Rails. That converts the "Eastern Time (US & Canada) that is contained in Canvas Data 2 with "America/New_York" that should work with PostgreSQL / Redshift. (I say should because I don't use PostgreSQL or Redshift).

[Jo-lCollins]

@robotcars @James 

Sorry I should have been more clear. I am requesting instructure provide the timezone code. I'm aware adding columns to the synchronized DAP data would cause problems, and that is not what I was proposing. 

I previously used the assets live events stream, and that provided a time zone code that could be used to convert UTC to the user's time zone, but the live events stream experiences random gaps in data coverage that have proven problematic for analysis so I am instead collecting user page views via the rest api, which is providing 100% data coverage. The page views endpoint does not provide the user's time zone. If this data could be added to the endpoint's return result that would also work. I could activate the assets live stream just to pull the most recent time zone code for a user, but given the size of assets data, and its redundancy with page views, this solution  feels a bit wasteful of resources.

The purpose of this post is to ask instructure's dev team if there is a way for them to provide information via Canvas Data 2 so analytic teams can analyze student activity according to the student's time zone. I am not asking for solutions for manually mapping UTC to student time zones myself.

[James]

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

[Jo-lCollins]

Hi @James 

Thank you for this information, but I do not think your response satisfies my question. To address some of your main points...

"trying to report times or include specialized time zone offsets is a bad idea and not something that Canvas should do"

When a student sets their time zone, this changes the time reporting a student interacts with within the Canvas LMS. I would like to be able to run analyses that reflect what the student is experiencing and how that relates to average student behavior. That is not something I consider a "bad idea", and it is something Canvas does do. 

Different database languages require different conversion codes

This is a good flag, and something that is relevant for Canvas Data 2 devs to consider. However, I do not think this blocks their ability to provide a machine translatable data point for converting UTC timestamps to the student's set local time. 

Timestamps should be stored as UTC

I agree. I am not asking timestamps to be stored in local time.

Time zones in canvas are unlikely to change, you should just map them yourself

I am requesting that mapping to be provided by Canvas Data 2.

"One downside is that people may change their time zones. I doubt that's a very big issue."

If a user changes their time zone, that change should be reflected in the users table when Canvas Data 2 syncs. If the time zone mappings were provided by CD2, the problem you describe here would not exist.

"Not all users set their time zone"

This is an institution level problem, not an issue with the data. 86% of users at my institution have set their time zone. While I appreciate you sharing your experience with these datasets, anecdotal examples like this are irrelevant. 

A student may travel outside their set time zone.

This is irrelevant. I would like to be able to build datasets that show the time a student interacts with within the LMS. Additionally, this example describes rare behavior that does not reflect the vast majority of students. 

Because of outlier student behavior, there would be error in time zone conversions, so you might as well just use Live Events, which also has error. Bad data is bad data so what's the difference?

The difference is in the type of error. Live events experiences missing records due to (what appears to be) machine error. Engineering teams I've worked with or communicated with who use the live events service have to supplement the service with API calls to ensure their data is complete. The Live Events error is more egregious when analyzing student engagement than the "error" caused by students traveling or failing to set their time zone, which (in my view) represents variance within student behavior that statistical tooling and data cleaning techniques are built to address.

There is a difference between time zone reporting in Live Events vs Canvas Data 2

While this is an interesting observation I do not see how this is relevant to my question "Can time zone mappings be provided within Canvas Data 2?". Though, I suppose it would be relevant if time zone was added to the return result from the rest api's page views endpoint

tagging @sgergely, because you liked James' response, and based on this post I think you work on the DAP development project. It's an excellent tool! 👋

[James]

@Jo-lCollins 

Your original post ended with "Alternatively, let me know if ya'll know of a better solution! Thank you!"

Based on your comments, you do not find my solution better, so I'm stopping my end of this conversation. I wish you the best of luck.

[reynlds]

Barring new development from Instructure, I also feel that an externally managed "transform" or "alias" process on the time zones is the best path forward. You are correct in that Live Events do not label the TZ's the same as are stored in the canvas.users table in CD2. However, it should be fairly trivial to setup and maintain the occasional TZ update on an external system and use that when querying CD2 events and objects. I counted the time zones in my CD2 instance and there are less than 100 (actually 91).

The larger issue to me is that Instructure is somewhat inconsistent with timestamps across the CD2 and other event data, choosing to record some things in UT and other things in your "local server" TZ. They also play "fast and loose" with some of their labels between the GUI and CD2/Live Events. Understanding these differences is the first step to mitigating them or permanently fixing them in the product.

Lots of good info provided in these posts!