What does the created_at field in the submission_dim represent?

[apritchett]

I have a question about the submission_dim and determining the difference between the submitted_at and created_at fields.  The submitted_at field matches the time stamp in Canvas that the submission was made but I can't figure out what the created_at field matches.  Anyone have some insight to set me straight?

[brenden_goetz]

Hi Aaron -

I did some investigating, and there are a few things I noted:

• created_at, submitted_at, AND updated_at AND graded_at are all important timestamp fields in understanding what is going on with submissions
• submission_type also seems to be fairly helpful, and there are some patterns for each timestamp listed above within the various submission types
• with "online_quiz" submission types, the relationship with the timestamp looks fairly intuitive
  1. created_at = when the person started the quiz
  2. submitted_at = when the person submitted the quiz
  3. submitted_at = updated_at = graded_at when the grade_state = "auto_graded" 
  4. when grade_state is not "auto-graded" (e.g. it's "human_graded"), then updated_at tends to reflect the graded_at time, although not always. It's worth looking into this more
• with "online_text_entry" submission types, created_at = submitted_at = updated_at  in most of the cases I looked at WHEN THE ASSIGNMENT WAS NOT YET GRADED. When graded, updated_at = graded_at. There is auto-save functionality within Canvas for some things, so one guess is that created_at is when that object was actually created - a quiz object is created when you start a quiz, and that same object gets updated when you finish the quiz. Same with text entries - when you start typing, it creates a text entry object, and perhaps it is auto-saved multiple times until you finally click "submit." I'm not totally sure why created_at = submitted_at = updated_at when not graded unless students are writing outside of Canvas and pasting into the text box (otherwise I would think created_at would be when the student started typing,  auto-saves would occur and possibly update the updated_at and submitted_at fields)

That's my first shot at it. I think auto-save and submission types are two factors in interpreting the table. Let me know if you figure out more, and I'll do the same.

[James]

 @brenden_goetz ‌, great work!

When I looked at this, I said "I don't have time to look at this." I'm glad someone else took it on.

I will say that for the online text entry submissions, nothing is created when the student starts typing and there isn't an auto-save, although there have been some feature requests for that. Jive auto saves text while creating comments, but Canvas doesn't. There is even a feature request "under development" since 2015 with delivery during Q1 or Q2 of 2016 that says the new TinyMCE has a plugin for this: https://community.canvaslms.com/ideas/1300 

I opened up the developer tools and watched the network traffic. There was nothing sent to the server other than to fetch the TinyMCE (Rich Content Editor) contents when I clicked on the "Submit Assignment" box to open up the text entry.

When I clicked Submit, then there was a POST made to the submissions page. This was not an API call, by the way, it was to an AJAX page within Canvas that handled things.The URL included the course and the assignment ID So it's similar to the Submit an assignment API endpoint except that the field names aren't the same.

All that was sent was a flag that it was in utf8, an authenticity token, the submission type of "online_text_entry" and the submission body that I had typed in. There was also a blank submission comment sent.

But that means that no information about when the person started typing was transmitted to Canvas.

When I retrieve the submission information through the API, I get

And you'll see that the only timestamp there is the submitted at.The graded at timestamp is added when it's graded.

There's not a created_at field there, which leaves the question where does Canvas Data come up with that at? Presumably, it's when Canvas processed it, but I did this test just now, so the data isn't into Canvas Data yet to verify. I looked at what is in Canvas Data and have 50,036 online_text_entry fields in my submissions_dim. 38,018 of those have the same created_at and submitted_at timestamps. 12,018 have different ones. Strangely, 764 of those 12,018 had a submitted_at timestamp that was before the created_at timestamp, but all of them were only by 1 second. Only 25 had a difference of less than 10 seconds, so it seems like it's something other than just processing delay involved. Of those with a positive difference (submitted_at > created_at) The average difference is 380,735 seconds (4.4 days) with a standard deviation of 1,370,050 seconds (15.8 days) suggesting that the data has some outliers and is skewed and the numbers I just gave are useless (there were 7 submissions where the submitted date was more than 6 months after the created date). Those were all in a non-teaching course and it makes you wonder if the person had clicked the "ignore for now" in the todo list.

It's not the workflow_state (11,649 graded, 369 submitted). It's not by whether it was graded anonymously (69 were, 11,949 weren't). It's not by grade state (11,776 human graded, 242 not_graded). The attempts are not all the same, either.

I opened up a text entry and let it sit for a while. The only thing sent to Canvas was a page view token that I had spent 19 interaction seconds with the page. But that was sent well after (minutes) I had moved off the page and was responding to this message, so 19 seconds can't be used to determine a starting time.

I left the text entry box open for about an hour and there was no autosave attempt. Only page_view_tokens and pings were sent back. Also, the submissions API doesn't returns the first submission because nothing has been created yet, it's all in the browser at this point. Once I click submit, then the submissions API returns the re-submission.

All this started off just to say there's no autosave on text entry. I sometimes get carried away in my analysis.

[apritchett]

Thanks to  @brenden_goetz  and  @James  for the great responses.  

One of the things I am looking for is for students who call into our Tech Support saying they submitted their paper but it isn't showing.  Most times it is an error with TurnItIn assignments where the student clicks "next" rather than "submit".  My hope was that I could pull a created_at time stamp of when they made the first attempt.  A couple days ago I went into Canvas and made some submissions with a test student and was able to pull the info from Canvas Data this morning.  Using the information provided above I was able to make some determinations on what the field is picking up.

The first test I did was a TurnItIn assignment that I clicked all the way to the "submit" and then clicked "next".  I then went back and submitted the paper properly.  I did all my actions on different minutes and recorded my times to compare with Canvas Data.  The results showed a submitted_at time stamp of the final complete attempt however the created_at time stamp actually came out to be 10 seconds later.  My assumption on this is the created_at time stamp came when Canvas received data back from TurnItIn; I will have to run further tests on this.  Unfortunately it didn't pick up the first attempt with a time stamp however it did pick it up in the attempt field which is interesting.

The second test I did was with a normal online upload of a paper.  As with the first test I went through the submission and instead of clicking "submit" I clicked "next. I then went back and submitted the paper properly and then came back and did a re-submission.  Canvas Data shows the created_at time stamp of the complete submission, it did not pick my failed attempt.  The submitted_at field shows the time of the second re-submission and the attempt field shows 2 attempts.

As  @James  stated their are some outliers that skew the data and will need to be accounted for.  The other interesting thing I see here is that Canvas picks up the attempt in an external_tool submission even if doesn't pick up the time stamp where as the online_upload picks up only the actual submission attempts. 

I will continue to run some tests and try to narrow it down and post updates if I find any new info, Thanks again for the great info!