MySQL – How to Retrieve Rows of Different Dates Using IN Clause

date formatMySQLPHPphpmyadmin

My table structure:

+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| user            | varchar(100) | NO   |     | NULL              |                |
| uniq_name       | varchar(100) | NO   |     | NULL              |                |
| info_name       | varchar(100) | NO   |     | NULL              |                |
| delay_time      | int(11)      | NO   |     | NULL              |                |
| track_time      | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+-----------------+--------------+------+-----+-------------------+----------------+

I have to retrieve data from table for past seven days, not summed one. Data for a specific date, for this right now I have to fired same query seven times using like operator:

SELECT COUNT(DISTINCT `user`) FROM `tracks` WHERE `track_time` LIKE '2012-11-12%';

Which is not a good way to do. I am trying to do myself, also discussed on forums , it might be done with IN operator or using date1 < date2 < ….date for past seven days. For this, i am using a function in PHP in which I am supplying a date parameter, but query will take lot of time to display result on web. Please have a look on this situation.

Best Answer

If you are doing a range of consecutive dates, there is no need to use the IN operator.

If you use this to compute midnight seven days ago

DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND

here is what you get back:

mysql> SELECT DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND;
+--------------------------------------------------+
| DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND |
+--------------------------------------------------+
| 2012-11-21 00:00:00                              |
+--------------------------------------------------+
1 row in set (0.01 sec)

mysql>

Your query will look like this:

SELECT COUNT(DISTINCT `user`) FROM `tracks`
WHERE `track_time` >= ( DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND );

You also need an index to support the WHERE and COUNT

ALTER TABLE tracks ADD INDEX track_time_user_ndx (track_time,user);

Then, the query should go faster.

If you are doing a range of consecutive dates from some earlier period, simply supply the first and last date from midnight to midnight. For example, for the range 2012-11-12 to 2012-11-19:

SELECT COUNT(DISTINCT `user`) FROM `tracks`
WHERE `track_time` >= '2012-11-12 00:00:00'
AND   `track_time` <  '2012-11-19 00:00:00';

If you are doing a range of non-consecutive dates, simply structure the query using UNION. For example, for dates 2012-11-12,2012-11-19,2012-11-26:

SELECT COUNT(DISTINCT `user`) FROM
(
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-12 00:00:00'
    AND   `track_time` <= '2012-11-12 23:59:59'
    UNION
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-19 00:00:00'
    AND   `track_time` <= '2012-11-19 23:59:59'
    UNION
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-26 00:00:00'
    AND   `track_time` <= '2012-11-26 23:59:59'
) A;

Give it a Try !!!

UPDATE 2012-11-27 13:52

If you want count of distinct users for just one date 2012-11-12

SELECT COUNT(DISTINCT user) usercount FROM `tracks`
WHERE `track_time` >= '2012-11-12 00:00:00'
AND   `track_time` <= '2012-11-12 23:59:59';

If you want count of distinct users for 7 consecutive dates 2012-11-12-2012-11-18 (You cannot include 2012-11-19)

SELECT
    DATE(track_time) track_date,
    COUNT(DISTINCT user) usercount
FROM
(
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-12 00:00:00'
    AND   `track_time` <  '2012-11-19 00:00:00'
) A GROUP BY DATE(track_time);

UPDATE 2012-11-27 14:41 EDT

If you want this dynamically done as of NOW(), try this:

SELECT
    DATE(track_time) track_date,
    COUNT(DISTINCT user) usercount
FROM
(
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= ( DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND )
) A GROUP BY DATE(track_time);
Related Question