Data from 4 days ago

[stan003]

Hello,

 

I am trying to put together a mysql query that will return the number of visitors for four days ago, what i am trying to do is plot the last seven days visitors on a graph in the format of day seven, day six, etc and need to find away to get a count for each of those days.  At the moment i am thinking about running various queries with each returning the results for a specific day.  The code below is supposed to get the count of visitors four days ago.  Not between now and four days ago but just for the 24 hour period which covers day 4.  The current code just returns a value of 0.  Any help would be appreciated.

 

$result = mysql_query("SELECT ip FROM ip_stats WHERE  date= date_sub(NOW(), interval 4 DAY)");
$num_rows = mysql_num_rows($result);
echo "$num_rows";

[xyph]

Your problem is that you're using DATETIME values rather than just DATE

 

You can convert a DATETIME to DATE using the DATE() function. Use CURDATE() to get the current DATE (NOW() returns the current DATETIME)

 

SELECT COUNT(*) as `count` FROM  `table` WHERE  DATE(`date`) = DATE_SUB( CURDATE() , INTERVAL 4 DAY )

 

Here's a way to get the last 7 days in a single query.

 

mysql> SELECT 7 as `day`, COUNT(*) as `count` FROM  `table` WHERE  DATE(`date`) = DATE_SUB( CURDATE() , INTERVAL 7 DAY )
    -> UNION
    -> SELECT 6 as `day`, COUNT(*) as `count` FROM  `table` WHERE  DATE(`date`) = DATE_SUB( CURDATE() , INTERVAL 6 DAY )
    -> UNION
    -> SELECT 5 as `day`, COUNT(*) as `count` FROM  `table` WHERE  DATE(`date`) = DATE_SUB( CURDATE() , INTERVAL 5 DAY )
    -> UNION
    -> SELECT 4 as `day`, COUNT(*) as `count` FROM  `table` WHERE  DATE(`date`) = DATE_SUB( CURDATE() , INTERVAL 4 DAY )
    -> UNION
    -> SELECT 3 as `day`, COUNT(*) as `count` FROM  `table` WHERE  DATE(`date`) = DATE_SUB( CURDATE() , INTERVAL 3 DAY )
    -> UNION
    -> SELECT 2 as `day`, COUNT(*) as `count` FROM  `table` WHERE  DATE(`date`) = DATE_SUB( CURDATE() , INTERVAL 2 DAY )
    -> UNION
    -> SELECT 1 as `day`, COUNT(*) as `count` FROM  `table` WHERE  DATE(`date`) = DATE_SUB( CURDATE() , INTERVAL 1 DAY );
+-----+-------+
| day | count |
+-----+-------+
|   7 |     2 |
|   6 |     1 |
|   5 |     2 |
|   4 |     1 |
|   3 |     1 |
|   2 |     0 |
|   1 |     1 |
+-----+-------+
7 rows in set (0.00 sec)

 

Where `count` is the number of rows per given day.

[stan003]

Thanks for the reply, so i need to convert my date/time field to just date?

[xyph]

Within the query, yes. DATE() will return only the YYYY-MM-DD portion of your DATETIME column.

 

Don't modify your table

 

mysql> SELECT DATE('2012-02-22 3:40:00') as date;
+------------+
| date       |
+------------+
| 2012-02-22 |
+------------+
1 row in set (0.00 sec)

[stan003]

hi sorry now i am confused, it's not possible to use that SELECT DATE('2012-02-22 3:40:00') as date; with the previous query you showed me is it?

 

SO there fore would i just run a query with this code and then use that to populate the second query if that makes sense.

 

Thanks