Generate Date from a range in Mysql and pad the results where no date is present


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..


, , , , , , , ,

  1. No comments yet.
(will not be published)