Extend to select based on data range

Question

I've got the following MySQL query:

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',
              u.id,
              '&mode=alllogs">',
              u.firstname ,' ',
              u.lastname,'</a>') AS Username, 
       count(*) AS logins ,
       (SELECT count(*) 
        FROM mdl_log 
        WHERE userid = l.userid 
        GROUP BY userid) AS Activity 
FROM mdl_log AS l 
JOIN mdl_user AS u ON l.userid = u.id 
WHERE action LIKE '%login%' 
GROUP BY userid 
ORDER BY Activity DESC 
INTO OUTFILE '/tmp/Total_Logins_With_Total_Activity.txt';

I want to extend this so that I can results based on a data range, say 365 days for instance.

So I'm thinking I can alter the above query to select the 'time' field from the mdl_logs table and covert that to days (Its a unix timestamp) and then only select when the time is within the last 365 day period. Any help appreciated.

The mdl_log table has the following structure:

+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| time   | int(10) unsigned | NO   | MUL | 0       |                |
| userid | int(10) unsigned | NO   | MUL | 0       |                |
| ip     | varchar(15)      | NO   |     |         |                |
| course | int(10) unsigned | NO   | MUL | 0       |                |
| module | varchar(20)      | NO   |     |         |                |
| cmid   | int(10) unsigned | NO   | MUL | 0       |                |
| action | varchar(40)      | NO   | MUL |         |                |
| url    | varchar(100)     | NO   |     |         |                |
| info   | varchar(255)     | NO   |     |         |                |
+--------+------------------+------+-----+---------+----------------+

The mdl_user table looks like:

+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| id            | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| auth          | varchar(20)         | NO   | MUL | manual  |                |
| confirmed     | tinyint(1)          | NO   | MUL | 0       |                |
| policyagreed  | tinyint(1)          | NO   |     | 0       |                |
| deleted       | tinyint(1)          | NO   | MUL | 0       |                |
| mnethostid    | bigint(10)          | NO   | MUL | 0       |                |
| username      | varchar(100)        | NO   |     |         |                |
| password      | varchar(32)         | NO   |     |         |                |
| idnumber      | varchar(255)        | NO   | MUL |         |                |
| firstname     | varchar(100)        | NO   | MUL |         |                |
| lastname      | varchar(100)        | NO   | MUL |         |                |
| email         | varchar(100)        | NO   | MUL |         |                |
| emailstop     | tinyint(1) unsigned | NO   |     | 0       |                |
| icq           | varchar(15)         | NO   |     |         |                |
| skype         | varchar(50)         | NO   |     |         |                |
| yahoo         | varchar(50)         | NO   |     |         |                |
| aim           | varchar(50)         | NO   |     |         |                |
| msn           | varchar(50)         | NO   |     |         |                |
| phone1        | varchar(20)         | NO   |     |         |                |
| phone2        | varchar(20)         | NO   |     |         |                |
| institution   | varchar(40)         | NO   |     |         |                |
| department    | varchar(30)         | NO   |     |         |                |
| address       | varchar(70)         | NO   |     |         |                |
| city          | varchar(20)         | NO   | MUL |         |                |
| country       | varchar(2)          | NO   | MUL |         |                |
| lang          | varchar(30)         | NO   |     | en_utf8 |                |
| theme         | varchar(50)         | NO   |     |         |                |
| timezone      | varchar(100)        | NO   |     | 99      |                |
| firstaccess   | int(10) unsigned    | NO   |     | 0       |                |
| lastaccess    | int(10) unsigned    | NO   | MUL | 0       |                |
| lastlogin     | int(10) unsigned    | NO   |     | 0       |                |
| currentlogin  | int(10) unsigned    | NO   |     | 0       |                |
| lastip        | varchar(15)         | NO   |     |         |                |
| secret        | varchar(15)         | NO   |     |         |                |
| picture       | tinyint(1)          | NO   |     | 0       |                |
| url           | varchar(255)        | NO   |     |         |                |
| description   | text                | YES  |     | NULL    |                |
| mailformat    | tinyint(1) unsigned | NO   |     | 1       |                |
| maildigest    | tinyint(1) unsigned | NO   |     | 0       |                |
| maildisplay   | tinyint(2) unsigned | NO   |     | 2       |                |
| htmleditor    | tinyint(1) unsigned | NO   |     | 1       |                |
| ajax          | tinyint(1) unsigned | NO   |     | 1       |                |
| autosubscribe | tinyint(1) unsigned | NO   |     | 1       |                |
| trackforums   | tinyint(1) unsigned | NO   |     | 0       |                |
| timemodified  | int(10) unsigned    | NO   |     | 0       |                |
| trustbitmask  | int(10) unsigned    | NO   |     | 0       |                |
| imagealt      | varchar(255)        | YES  |     | NULL    |                |
| screenreader  | tinyint(1)          | NO   |     | 0       |                |
+---------------+---------------------+------+-----+---------+----------------+

+1 for including relevant table structures. (Would make that +10, if I could...) — , Nov 11 '11 at 10:21

score 1 · Answer 1 · answered Nov 11 '11 at 10:33

Mark's answer is good and gives you the last 365 days, adding an upper bound on the interval is trivial, however the query is less efficient than it could be - reading from mdl_log twice:

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',
          u.id,
          '&mode=alllogs">',
          u.firstname ,' ',
          u.lastname,'</a>') AS Username,
   SUM(IF(l.action LIKE '%login%', 1, 0)) AS logins,
   COUNT(*) AS Activity 
FROM mdl_log AS l 
  JOIN mdl_user AS u ON l.userid = u.id 
WHERE l.time BETWEEN 
  UNIX_TIMESTAMP(ADDDATE(now(),INTERVAL -200 day)
    AND UNIX_TIMESTAMP(ADDDATE(now(),INTERVAL -100 day)
GROUP BY userid
HAVING  SUM(IF(l.action LIKE '%login%', 1, 0))>0 
ORDER BY Activity DESC
INTO OUTFILE '/tmp/Total_Logins_With_Total_Activity.txt';

score 0 · Accepted Answer · answered Nov 11 '11 at 10:20

Queries are likely to perform better if you compare the unconverted table column to a value of its own type - like so:

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',
              u.id,
              '&mode=alllogs">',
              u.firstname ,' ',
              u.lastname,'</a>') AS Username, 
       count(*) AS logins ,
       (SELECT count(*) 
        FROM mdl_log 
        WHERE userid = l.userid 
        GROUP BY userid) AS Activity 
FROM mdl_log AS l 
JOIN mdl_user AS u ON l.userid = u.id 
WHERE l.action LIKE '%login%' AND
      l.time >= unix_timestamp(adddate(date(now()),interval -365 day)
GROUP BY userid 
ORDER BY Activity DESC 
INTO OUTFILE '/tmp/Total_Logins_With_Total_Activity.txt';

You could use interval -1 year, if you wanted to take account of leap years.

Extend to select based on data range

2 Answers2