If you want to generate dates, MYSQL doesn’t have a range function, so I wrote a work around for the same. So how it works is I have reused a code to generate numbers from my previous post. So I added date function to that query and reduce the generated numbers from the date. Hence effectively its Currrent Date – x number of days. Thus we generate the dates from the date we provide to the x number we generate. So here is the code:
select @rownum:=@rownum+1,date('2010-07-13') - interval @rownum day 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 @rownum:=0) r
This query gives the following:
+——————–+——————————————-+
| @rownum:=@rownum+1 | date(’2010-07-13′) – interval @rownum day |
+——————–+——————————————-+
| 1 | 2010-07-12 |
| 2 | 2010-07-11 |
| 3 | 2010-07-10 |
| 4 | 2010-07-09 |
| 5 | 2010-07-08 |
| 6 | 2010-07-07 |
| 7 | 2010-07-06 |
| 8 | 2010-07-05 |
| 9 | 2010-07-04 |
| 10 | 2010-07-03 |
| 11 | 2010-07-02 |
| 12 | 2010-07-01 |
| 13 | 2010-06-30 |
| 14 | 2010-06-29 |
| 15 | 2010-06-28 |
and so on..
The power of this code is if you need to pad a result of a query such as the following:
select * from date;
which gives result as:
+————+——–+
| date | number |
+————+——–+
| 2010-06-21 | 11 |
| 2010-07-21 | 1 |
| 2010-06-12 | 21 |
+————+——–+
Now if you need the result to print all dates and if row is missing then print as null we change to query to the following:
SELECT allDate.date,date.number from date
RIGHT OUTER JOIN (select @rownum:=@rownum+1 as rownum,date('2010-07-13') - interval @rownum day as date 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 @rownum:=0)r) allDate
ON allDate.date=date.date;
This gives the result as:
+————+——–+
| date | number |
+————+——–+
| 2010-07-12 | NULL |
| 2010-07-11 | NULL |
| 2010-07-10 | NULL |
| 2010-07-09 | NULL |
| 2010-07-08 | NULL |
| 2010-07-07 | NULL |
| 2010-07-06 | NULL |
| 2010-07-05 | NULL |
| 2010-07-04 | NULL |
| 2010-07-03 | NULL |
| 2010-07-02 | NULL |
| 2010-07-01 | NULL |
| 2010-06-30 | NULL |
| 2010-06-29 | NULL |
| 2010-06-28 | NULL |
| 2010-06-27 | NULL |
| 2010-06-26 | NULL |
| 2010-06-25 | NULL |
| 2010-06-24 | NULL |
| 2010-06-23 | NULL |
| 2010-06-22 | NULL |
| 2010-06-21 | 11 |
| 2010-06-20 | NULL |
| 2010-06-19 | NULL |
| 2010-06-18 | NULL |
| 2010-06-17 | NULL |
| 2010-06-16 | NULL |
| 2010-06-15 | NULL |
| 2010-06-14 | NULL |
| 2010-06-13 | NULL |
| 2010-06-12 | 21 |
and so on..
So try it out and tell me how it goes..
Recent Comments