Which SQL Table and column captures course activity completion timestamps?

Which SQL Table and column captures course activity completion timestamps?

by Rashid Habib -
Number of replies: 2

Hello,


I've been working on custom SQL reports on primarily SCORM activities within a given course. The scorm table from the sql database shows start time but not completion timestamp. In layman's terms I can know when a learner starts a SCORM activity but not when it is completed. It does tell me how much time is spent - which is a good statistic - but I want to close it off with the completion date and time.


On the other hand, Moodle course-level reports - namely Activity Completion, seem to populate this information with ease. They show the timestamp of completion for every applicable activity in the course (be it SCORM or non-scorm). However, I cannot identify which sql table and/or columns it retrieves this data from. Does anyone know where in the database specifically this is stored?

Average of ratings: -
In reply to Rashid Habib

Re: Which SQL Table and column captures course activity completion timestamps?

by Melanie Scott -
Picture of Particularly helpful Moodlers
I think what you're looking for is mdl_course_modules_completion which links into mdl_course_modules.  Where else they attach, I don't really know.  I don't typically use that data but I have on very rare occasions. Makes my head hurt to wade through tables.

Noticed your post and since I'm going to be asking a question about tables shortly, I figured I'd pay it forward first...
Average of ratings: -
In reply to Rashid Habib

Re: Which SQL Table and column captures course activity completion timestamps?

by Randy Thornton -
Picture of Documentation writers


Rashid

The completion for each user's activity is kept in the mdl_course_modules_completion table. 

The 'completionstate' field indicates whether the user has completed the activity, where the values are: 0 = not complete, 1=complete, 2=completepass, 3= completefail. The timemodified column tells you when that was last set, which for completions will be the completion time and date.

The mdl_course_modules table tells you about the settings for each activity's completion. The 'completion' fields tells you the activity completion type set for each activity where 0 = none, 1 = self-completion, 2 = auto-completion.   


These two tables are joined by course_modules_completion.coursemoduleid = course_modules.id


Randy

Average of ratings: Useful (2)