Can somebody help me? I am trying to make a nice report on user log. I exported table mdl_user into xbase format (.dbf), and wonder what column lastaccess, lastlogin and currentlog mean? I am sure they are time stamp for last access, last login and current log, but how to convert them? For example, '1228458189' means what date or what time ?
If I want to make a complete report, something like an excell format on admin->report->log, what table do I need ? (mdl_user, mdl_log,...what else?)
Thank you!
In reply to Sulaiman Hana Permana
Re: content of column lastaccess, lastlogin, current
by John White -
Hi,
The values you are seeing are in UNIX TIME format.
You can convert them with a formatting function in your SQL statement, and then export the result:
For example:
If I enter the SQL statement:
...I get a column headed `lastaccess_datetime` with all Unix times converted
(note: here I have elimated zero dates, you don't have to do that, as it may eliminate loads or users, but you will find zero dates yield Jan 1st 1970 1am - the starting point of Unix Time!!!)
So you just use...
SELECT FROM_UNIXTIME( lastaccess, '%Y %D %M %h:%i:%s') AS lastaccess_datetime FROM mdl_user;
However, once you have exported this all to Excel you will find sorting the tables by this revised datetime column a nightmare so it would be better to keep the original lastaccess column and use that to sort on if needed...
So...
SELECT * , FROM_UNIXTIME( lastaccess, '%Y %D %M %h:%i:%s' ) AS lastaccess_datetime FROM mdl_user;
...selects the entire table * and then , selects the converted lastaccess.
You can add in the other datetime fields too, for example...
SELECT * , FROM_UNIXTIME( firstaccess, '%Y %D %M %h:%i:%s' ) AS firstaccess_datetime, FROM_UNIXTIME( lastaccess, '%Y %D %M %h:%i:%s' ) AS lastaccess_datetime FROM mdl_user;
As for what you might need in the way of tables, perhaps this is more a question of what you might need to do to join two or more tables. For example:
SELECT * FROM (SELECT id, username, firstname, lastname FROM `mdl_user`) AS U INNER JOIN `mdl_log` AS L WHERE U.id=L.userid AND U.id=2;
Gets a log about user 2, but puts a name to the id.
There's lots more to explore at http://dev.mysql.com/doc/refman/5.0/en/
...but thats a whole new story!
Hope this helps a bit,
John
The values you are seeing are in UNIX TIME format.
You can convert them with a formatting function in your SQL statement, and then export the result:
For example:
If I enter the SQL statement:
SELECT FROM_UNIXTIME( 1228458189, '%Y %D %M %h:%i:%s' );
...I find that 1228458189 means 2008 5th December 06:23:09
(changing the order of the formatters to %D %M %Y gives day-month-year)
So if I apply this to `lastaccess` I try...
SELECT FROM_UNIXTIME( lastaccess, '%Y %D %M %h:%i:%s') AS lastaccess_datetime FROM mdl_user WHERE lastaccess>0;...I find that 1228458189 means 2008 5th December 06:23:09
(changing the order of the formatters to %D %M %Y gives day-month-year)
So if I apply this to `lastaccess` I try...
...I get a column headed `lastaccess_datetime` with all Unix times converted
(note: here I have elimated zero dates, you don't have to do that, as it may eliminate loads or users, but you will find zero dates yield Jan 1st 1970 1am - the starting point of Unix Time!!!)
So you just use...
SELECT FROM_UNIXTIME( lastaccess, '%Y %D %M %h:%i:%s') AS lastaccess_datetime FROM mdl_user;
However, once you have exported this all to Excel you will find sorting the tables by this revised datetime column a nightmare so it would be better to keep the original lastaccess column and use that to sort on if needed...
So...
SELECT * , FROM_UNIXTIME( lastaccess, '%Y %D %M %h:%i:%s' ) AS lastaccess_datetime FROM mdl_user;
...selects the entire table * and then , selects the converted lastaccess.
You can add in the other datetime fields too, for example...
SELECT * , FROM_UNIXTIME( firstaccess, '%Y %D %M %h:%i:%s' ) AS firstaccess_datetime, FROM_UNIXTIME( lastaccess, '%Y %D %M %h:%i:%s' ) AS lastaccess_datetime FROM mdl_user;
As for what you might need in the way of tables, perhaps this is more a question of what you might need to do to join two or more tables. For example:
SELECT * FROM (SELECT id, username, firstname, lastname FROM `mdl_user`) AS U INNER JOIN `mdl_log` AS L WHERE U.id=L.userid AND U.id=2;
Gets a log about user 2, but puts a name to the id.
There's lots more to explore at http://dev.mysql.com/doc/refman/5.0/en/
...but thats a whole new story!
Hope this helps a bit,
John