I was assigned the task of getting some statysticis from our moodle 1.7 / MySQL.
The job is quite simple : how many students do we have in december 2006, and how many in december 2007? Same for courses, and teachers.
I'm trying to get the job done by parsing the mdl_log table, but I dont understand the format of the time field - int(10).
I have registers like 1121457458.
Anyone knows how can I convert that field in something I can use in a MySQL query?
That is a UNIX timestamp, so you probably want to use the FROM_UNIXTIME function, documented here: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html
I use MySQL Query Browser and use the following:
select count(distinct `mdl_log`.`userid`) as 'Users',
date_format(from_unixtime(`mdl_log`.`time`),'%M %Y') AS `Date`
from `mdl_log`
where ((`mdl_log`.`userid` <> '1')
and (`mdl_log`.`action` = 'view'))
group by 'Date'
order by mdl_log.time asc
The line date_format(from_unixtime(`mdl_log`.`time`),'%M %Y') AS `Date` converts the Unix Time Format to month and year.
I use MySQL Query Browser and use the following:
select count(distinct `mdl_log`.`userid`) as 'Users',
date_format(from_unixtime(`mdl_log`.`time`),'%M %Y') AS `Date`
from `mdl_log`
where ((`mdl_log`.`userid` <> '1')
and (`mdl_log`.`action` = 'view'))
group by 'Date'
order by mdl_log.time asc
The line date_format(from_unixtime(`mdl_log`.`time`),'%M %Y') AS `Date` converts the Unix Time Format to month and year.