MySQL Count data for the last 7 days

Question

I have the following diagram.

Voices table

+------------------+--------------+------+-----+---------------------+----------------+
| Field            | Type         | Null | Key | Default             | Extra          |
+------------------+--------------+------+-----+---------------------+----------------+
| id               | int(10)      | NO   | PRI | NULL                | auto_increment |
| aid              | varchar(10)  | NO   |     |                     |                |
| ip               | varchar(100) | NO   |     |                     |                |
| host             | varchar(200) | NO   |     |                     |                |
| timestamp        | varchar(20)  | NO   |     | 0000-00-00 00:00:00 |                |
| user             | tinytext     | NO   |     | NULL                |                |
| userid           | int(10)      | NO   |     | 0                   |                |
+------------------+--------------+------+-----+---------------------+----------------+

Here I want to get a count of each help for one day for the last 7 days from "0" for dates where there are no votes for help. timestamp is the unix timestamp here.

Any help is appreciated.

Answer 1

MySQL does not have recursive functionality, so you just have to use the NUMBERS table trick -

• Create a table containing only incremental numbers - easy to do with auto_increment:

  DROP TABLE IF EXISTS `example`.`numbers`;
  CREATE TABLE  `example`.`numbers` (
    `id` int(10) unsigned NOT NULL auto_increment,
     PRIMARY KEY  (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  

• Fill in the table using:

  INSERT INTO NUMBERS
    (id)
  VALUES
    (NULL)
  

  ... for the number of values ​​you need.

• DATE_ADD , NUMBERS.id. "2010-01-01" "2010-01-02" ( , -- : : ). NUMBERS.id CURRENT_DATE, -

  SELECT x.dt
    FROM (SELECT DATE_SUB(CURRENT_DATE, INTERVAL (n.id - 1) DAY) AS dt
            FROM numbers n
           WHERE n.id <= 7 ) x
  

• LEFT JOIN , datetime.

     SELECT x.dt,
             COUNT(v.aid) AS num
       FROM (SELECT DATE_SUB(CURRENT_DATE, INTERVAL (n.id - 1) DAY) AS dt
               FROM numbers n
              WHERE n.id <= 7 ) x
  LEFT JOIN VOTES v ON DATE(FROM_UNIXTIME(v.timestamp)) = DATE(x.dt)
   GROUP BY x.dt
   ORDER BY x.dt
  

, ?

, . , , .

:

  SELECT DATE(FROM_UNIXTIME(v.timestamp)) AS dt,
         COUNT(v.aid)
    FROM VOTES v
   WHERE DATE(FROM_UNIXTIME(v.timestamp)) BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
                                              AND CURRENT_DATE
GROUP BY DATE(FROM_UNIXTIME(v.timestamp))

Answer 2

varchar (20) DATETIME, , . 7 WHERE timestamp > @7DaysAgo. , unix, - 7 . 0, , DATETIME.

SELECT COUNT (*) , FROM table WHERE timestamp > @7DaysAgo GROUP BY help