Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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);
share|improve this answer
    
I wan to display data for consecutive date ranges alone, means for date 2012-11-12: it shows distinct users, for another date it shows another one. Through above query, it shows me total distinct users between 12 to 19. –  shihon Nov 28 '12 at 18:41
    
hey, second option working for me, that was an awesome query to help me for retrieving different date ranges data, can you prefer me any tutorial or resources for work on query logics –  shihon Nov 28 '12 at 19:28
    
Perhaps Chapters 9 and 10 of "SQL for MySQL Developers" ( amazon.com/gp/product/0131497359 ) –  RolandoMySQLDBA Nov 28 '12 at 19:33
    
Hey, i want this query to be run as in real time scenario, like dates have to be dynamic. Should i do for seven days using DATE(track_time) and last one is DATE_SUB(CURDATE, INTERVAL 7 DAY) ? –  shihon Nov 28 '12 at 19:36
    
Updated my answer –  RolandoMySQLDBA Nov 28 '12 at 19:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.