Canvas Data 2 public table submissions column submitted_at returns inaccurate dates

[saustin]

Good afternoon, PostGres SQL queries using Canvas Data 2 for the public table, submissions column, submitted_at field returns inaccurate dates.

Below is an example of the results of a submitted_at field for a student in a class.   The top is null because the row was for a teacher and would not have submitted the assignment, the bottom row is for a student.  The date of the submitted_at is in 2023, however, the class did not start until 2024.

Here are the details of the start date of the course:

Course Start Date

Start

Local: Dec 27, 2023 12:00am

Course: Dec 27, 2023 12:00am

Course End Date

End

Local: Jan 14, 2024 11:59pm

Course: Jan 14, 2024 11:59pm

Restrict students from viewing course before course start date

Restrict students from viewing course after course end date

Is anyone else encountering this when using the submitted_at field in the submissions column in the public table of Canvas Data 2?  I welcome any insights you may have to offer.  Thank you so much.

[chriscas]

Hi @saustin,

I'm going to move tour question over to the Data and Analytics group, where there are more experts with this sort of thing.  The first thing I'd check is what the UI shows for the submission compared to what you see in CD2.  it could be that the course dates were changed from what they originally were, but it would certainly narrow the issue down to something with CD2 itself, or something more with the course/setup.

-Chris

[saustin]

Thank you, with the modified query, the UI of page views agrees with API calls and the database results -- so far.   The modified query involves working with some SQL I am not familiar with, windows and partitions.    When these functions are used, all is well.   But I still wonder why we even have to do that at all.

My apologies for not having more answers at this time, this is a very slow check.   I will come back as I find out more.   Thank you for moving this to the Data and Analytics group.

[Jeff_F]

@saustin  I just checked all the submissions for one of our recent terms that ran 01/08/24 to 03/10/24 and I do not see any with a 2023 date.  All 11,344 submissions show a submitted at timestamp after the course start date. We do permit late submissions after the term end date so the latest submission is from 3/22.

I am curious if the submitted_at column you shared is a calculated field where the time is changed from UTC to your time zone. If so, could the calculation have an error? Just an idea.

[saustin]

Thank you @Jeff_F That was my first thought, and something, I am still pursuing.

At least part of the problem is the way I asked for it in SQL; a more refined query brought up submission dates that made sense, but I still wonder why we had to go that route at all.   As I find out more, I'll post here, but because we were able to get correct results with a different query I don't think the calculation of the time zone has an error.

I think.

Thank you so much, I appreciate your feedback, and as I find out more, I will return to this.

[saustin · Accepted solution]

Please feel free to close this thread.   It was a bad. join on my part.

Thank you so much to everyone here for stepping up to help out.   You are the best!!!

[WillGHampton]

Hi Saustin,

You mentioned that you had to do a 'more refined query' and 'wonder why we had to go that route at all'

I'm having a similar problem, with pulling submissions for a date range in CD2 not matching what the gradebook shows. What was the query that did not work right and what was the query that did?

I am trying the following:

select

e.user_id as "User ID",

psra.sis_user_id as "Sis",

banner.Major_Desc as "Major",

(select count(finished_at)

from canvas.quiz_submissions qs, canvas.quizzes q

where q.id=qs.quiz_id and qs.user_id=e.user_id

and context_id=1638075

/*and to_char(qs.finished_at, 'yyyy-Mon-dd') between ('2023-05-01') and ('2024-04-30')*/

and qs.finished_at >= '2023-05-01'

and qs.finished_at <= '2024-04-30'

) as "Quiz Count",

(select count(completed_at)

from canvas.context_module_progressions cmp, canvas.context_modules cm

where cmp.context_module_id = cm.id

and cmp.user_id = e.user_id

and cm.context_id = 1638075

/*and to_char(cmp.updated_at, 'yyyy-Mon-dd') between ('2023-05-01') and ('2024-04-30')*/

and cmp.updated_at >= '2023-05-01'

and cmp.updated_at <= '2024-04-30'

) as "Module Count"

from canvas.enrollments e

inner join canvas.pseudonyms as psra on psra.id = e.sis_pseudonym_id

inner join canvas.banner_active_student_curriculum_detail as banner on banner.BLAZERID = psra.sis_user_id

where e.course_id=1638075

and e.type='StudentEnrollment'

group by "User ID","BlazerID","Graduate Level","Graduate Description", "Department", "Program","College","Degree","Major", "Quiz Count", "Module Count";

The query gets *some* of the submissions and module completions during the time frame, but not others.

I can try changing finished_at to submitted at, the way you talk of joins has me confused about what is proper and what is not.

Thanks