I'm trying to obtain the average time of an "activity" in a moodle database, i am not an sql expert, but i have managed to get to the point showed in the picture, my question is if exists a way to obtain, first the timestamp/time difference (this "activity" does not have a starting time column like many others) by day and then sum them all to get the average of that activity , for the first i tried with the function 'EXTRACT()' and comparing the dates in the format "%Y-%m-%d" but only sums the first row where they are equal, by the way i have been doing this just by a sql statement, i know the existence of store procedures but my level of sql is not that high. Thanks in advance! data obtained so far
Data on table logs (the most important i think)
| component | action | objecttable | userid | courseid | timecreated |
|---|---|---|---|---|---|
| mod_quiz* | viewed | quiz_attempts | 6 | 2 | 1645287525 |
| mod_forum | viewed | forum | 5 | 2 | 1645288525 |
| core | loggedout | user | 2 | 0 | 1645291745 |
| mod_page | viewed | page | 5 | 2 | 1645291955 |
Data i've trying to get:
| Activity | StartTime | EndTime | Total |
|---|---|---|---|
| forum | 19:01 | 19:10 | 9 minute(s) |
| quiz | 15:45 | 16:00 | 15 minute(s) |
| page | ... | ... | ... |
| workshop | ... | ... | ... |
but so far i get to assort the data in a column
| Time |
|---|
| 2022-x-x h:m |
| .... |
but when i try to sum by day with the function EXTRACT() and trying to match the dates in a very long query it just get the first record. NOTE: * half of the "activities" were easy to calculate since they have a "timestart" and "timeend" columns but i can not figure out how to solve the ones that do not have a "timestart" column.
