wanted to get all dates in mysql result

Question

I have mysql table called user(id, name, join_on) join on is a date field what I want is to show in each day how many uses has been created I can use group by but it will only give me the dates when users get added like if date

4/12/10  5 users added
4/13/10  2 users added
4/15/10  7 users added

here date 4/14/10 is missing and I want listing of all dates in one month. I have one solution for it by creating another table only for adding date and that table will left join my users table on join_on and will give total result but I don't want to do that as for creating that I need to create and add entries in date table please suggest the different approach for doing so.

Thank you.

Bienne

+1 I've done it in code (outside SQL) but very interesting to see if some clean SQL-only solutions will pop up – Accouplement

This is a commonly asked and answered question on SO (search for date and [sql] or [mysql]). See, e.g., #1047365 – Chela

@pilcrow: it is a commonly asked question, but the linked answer is not particularly exhaustive in the details. @OP: normally this sort of thing is not so easy in databases that do not support recursive SQL; still I posted some SQL (with some problems, but it might be useful) – Geochronology

Answer 1

There is an approach that can do this in pure SQL but it has limitations.

First you need to have a number sequence 1,2,3...n as rows (assume select row from rows return that).

Then you can left join on this and convert to dates based on number of days between min and max.

 select @min_join_on := (select min(join_on) from user);
 select @no_rows := (select datediff(max(join_on), @min_join_on) from user)+1;

will give you the required number of rows, which then you can use to

 select adddate(@min_join_on, interval row day) from rows where row <= @no_rows;

will return a required sequence of dates on which then you can do a left join back to the users table.
Using variables can be avoided if you use sub queries, I broke it down for readability.

Now, the problem is that the number of rows in table rows has to be bigger then @no_rows. For 10,000 rows you can work with date ranges of up to 27 years, with 100,000 rows you can work with date ranges of up to 273 years (this feels really bad, but I am afraid that if you don't want to use stored procedures it will have to look and feel awkward).

So, if you can work with such fixed date ranges you can even substitute the table with the query, such as this

SELECT @row := @row + 1 as row FROM (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, (SELECT @row:=0) r

which will produce 10,000 rows going from 1 to 10,000 and it will not be terribly inefficient at it.

So at the end it is doable in a single query.

create table user(id INT NOT NULL AUTO_INCREMENT, name varchar(100), join_on date, PRIMARY KEY(id));

mysql> select * from user;
+----+-------+------------+
| id | name  | join_on    |
+----+-------+------------+
|  1 | user1 | 2010-04-02 | 
|  2 | user2 | 2010-04-04 | 
|  3 | user3 | 2010-04-08 | 
|  4 | user4 | 2010-04-08 | 
+----+-------+------------+
4 rows in set (0.00 sec)

insert into user values (null, 'user1', '2010-04-02'), (null, 'user2', '2010-04-04'), (null, 'user3', '2010-04-08'), (null, 'user4', '2010-04-08')


SELECT date, count(id)
FROM (
SELECT adddate((select min(join_on) from user), row-1) as date 
FROM ( 
SELECT @row := @row + 1 as row FROM (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, (SELECT @row:=0) r ) n  
WHERE n.row <= ( select datediff(max(join_on), min(join_on)) from user) + 1
) dr LEFT JOIN user u ON dr.date = u.join_on
GROUP BY dr.date

+------------+-----------+
| date       | count(id) |
+------------+-----------+
| 2010-04-02 |         1 | 
| 2010-04-03 |         0 | 
| 2010-04-04 |         1 | 
| 2010-04-05 |         0 | 
| 2010-04-06 |         0 | 
| 2010-04-07 |         0 | 
| 2010-04-08 |         2 | 
+------------+-----------+
7 rows in set (0.00 sec)

Geochronology

Answer 2

It might be simpler to just use GROUP BY and then in your actual code add in the missing dates (or iterate through the entire date range and output a zero if the date is missing from the query results).

Not everything has to be solved in SQL, and many things are easier to solve elsewhere. :)

Charitacharitable

thanks for the reply but I want to do it only in sql as I want to do some more things with it, and besides that I will learn some more good thins too :) – Bienne

Answer 3

SELECT * FROM TABLE WHERE DATE LIKE '4/%/10'

This will give u all data for the month of april 2010

Similarly u can get any month's data by specifying month's numeric value ie 4 in this case

Jeffry

its not like I want to get date from range only I also want to get all other dates which is not in my records – Bienne

You should work with dates as dates, not strings. The above code might give you data for month of April or it might give you 4th of every month, or finally it might give you an error. – Geochronology

@PankajK : in that case u can have put the code in ur program a/c ur need. It could be done by queries but that will increase execution time – Jeffry