Mysql date_sub last 7 days result?

Question

In mysql i am trying to get last 7 days data so i tried with following query

date_sub(curdate(), interval 7 day) <= visited_time from visitor table gives date from 5/10/2013 to 5/16/2013

but here the same query date_sub(curdate(), interval 7 day) <= date_entered from order table gives date from 5/9/2013 to 5/15/2013

Today is 5/16/2013 so it should give 5/9/2013 to 5/15/2013 as last 7 days result for visitor table also.

whats wrong on this ? kindly advice

@ExplosionPills thanks, I checked the visitor table, there is no data on 5/9/2013. but even it has no data for 5/9/2013 it should return empty data for that particular date? am i right ? — mymotherland, May 16 '13 at 16:25
@ExplosionPills so i am doing wrong here.kindly advice on this — mymotherland, May 16 '13 at 16:28
I don't understand the question. The result seems to be what you would expect, so what's the problem. Consider providing an SQLFIDDLE to better illustrate the problem. — Strawberry, May 16 '13 at 17:02
You want to summarize (count) data for missing days in MySQL, which question has been [asked](http://stackoverflow.com/q/10034668) [before](http://stackoverflow.com/q/6551179) [here](http://stackoverflow.com/questions/3538858). — pilcrow, May 16 '13 at 17:15
possible duplicate of [MySQL how to fill missing dates in range?](http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) — pilcrow, May 16 '13 at 17:15

score 0 · Answer 1 · edited May 23 '17 at 12:11

I have followed the post and tried with Numbers table trick

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;

Populate the table using: INSERT INTO NUMBERS (id) VALUES (NULL) ...for as many values as you need.

Then finally tried with below query using left join with number table.

SELECT  x.ts AS TIMESTAMP
FROM (

SELECT DATE_ADD( CURDATE( ) , INTERVAL n.id -7
DAY ) AS ts
FROM numbers n
WHERE DATE_ADD( CURDATE( ) , INTERVAL n.id -7
DAY ) <= CURDATE( )
)x
LEFT JOIN orders y ON ( y.`date_entered` ) = x.ts
GROUP BY DATE( x.ts ) 
ORDER BY DATE( x.ts ) DESC

Mysql date_sub last 7 days result?

1 Answers1