How to select visitor counts by days

Question

I have this table from which I need to select visitors count by date.

CREATE TABLE `visitors` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip` VARCHAR(32) NOT NULL,
  `browser` VARCHAR(500) NOT NULL,
  `version` VARCHAR(500) NOT NULL,
  `platform` ENUM('w','l','m') NOT NULL,
  `date` DATE NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `person` (`ip`,`date`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

I try this:

SELECT DATE(`date`) AS `date`, COUNT(*) AS `visitor`
FROM `visitors` WHERE (`date` >= (NOW() - INTERVAL 7 DAY))
GROUP BY `date`;

Which returns this:

date         visitor
2015-12-19    2
2015-12-22    5

But what I really want is this:

date         visitor
2015-12-17    0        // Make 0 for the day which is not present
2015-12-17    0        // Make 0 for the day which is not present
2015-12-18    0        // Make 0 for the day which is not present
2015-12-19    2
2015-12-20    0        // Make 0 for the day which is not present
2015-12-21    0        // Make 0 for the day which is not present
2015-12-22    5

If I try with unions and get dates with this it is not problem with example 1 week. But what can I do if I need 1 year?

I do not think that it is good idea to create 1 table with dates... it need a lot of memory... I need 0 if it is not presents in table... this is only problem. — maia bardavelidze, Dec 22 '15 at 16:02
@maiabardavelidze sql cannot invent what is not there, so you **will need** some kind of a helper table. See the duplicate topic I linked above. — Shadow, Dec 22 '15 at 16:04
@maiabardavelidze A table holding all necessary dates is a depressingly small thing. — Strawberry, Dec 22 '15 at 16:05
@Strawberry you do not even need a calendar table, just a table that holds numbers 1 to 7, just as the linked topic describes — Shadow, Dec 22 '15 at 16:08
The date datatype requires 3 bytes of storage, this equates to 13kB per 100 years. I **really** don't think storage space will be an issue with a calendar table. — GarethD, Dec 22 '15 at 16:08
ok. so please tell me how to create calendar table and select this kind information from them? — maia bardavelidze, Dec 22 '15 at 16:16

Bernd Buffen · Accepted Answer · 2015-12-22T18:29:10.210

1

Use the sequence Engine. So you can generate date without a table

https://mariadb.com/kb/en/mariadb/sequence/

MariaDB [mysql]> SELECT date(now() - INTERVAL seq DAY) FROM seq_0_to_6;
+--------------------------------+
| date(now() - INTERVAL seq DAY) |
+--------------------------------+
| 2015-12-22                     |
| 2015-12-21                     |
| 2015-12-20                     |
| 2015-12-19                     |
| 2015-12-18                     |
| 2015-12-17                     |
| 2015-12-16                     |
+--------------------------------+
7 rows in set (0.00 sec)

This will work without a other Engine. You only must have a table with more Records as Days. The count of Days backwards is the LIMIT.

SELECT compare_date , count(v.`date`)
FROM (
    SELECT DATE(now() - INTERVAL @nr:=@nr+1 DAY)  AS compare_date 
    FROM information_schema.COLUMNS, (SELECT @nr:=-1) AS tmp 
    LIMIT 7
    ) AS d
LEFT JOIN  `visitors` v ON v.`date` = compare_date
GROUP BY compare_date;

Please tell this works for you.

edited Dec 22 '15 at 18:29

answered Dec 22 '15 at 16:26

Bernd Buffen

14,525
2
24
39

it is not working for me – maia bardavelidze Dec 22 '15 at 17:44
I do not understand how to use DATE_ADD in my case. can anyone help me??? I think it may help... – maia bardavelidze Dec 22 '15 at 18:04
@maia bardavelidze - I have add a other Query in at my answer. please test it. – Bernd Buffen Dec 22 '15 at 18:29
yes it is working now. and the last question. how can I do like this on monthes and years? example if I need year information I must get last monthes count from january and if I need all information I must get the years visits count... how can I do this? – maia bardavelidze Dec 22 '15 at 18:45

How to select visitor counts by days

1 Answers1