how to fill up the missing dates ??

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

how to fill up the missing dates ??

Post by PHPycho »

hello forums!!
I had the table1 as follows:
-----------------
| table1 |
-----------------
| id | visit_date | no_of_hits |
| 1 | 2002-02-12 | 3 |
| 2 | 2002-02-13 | 10 |
| 3 | 2002-02-15 | 200 |
:
:
| n-1| 2009-01-8 | 125 |
| n | 2009-01-10 | 400 |
Now i would like to show all dates between two date ranges (for example 2003-02-12 to 2008-12-30) which
may or may not have no of hits.
I know this can be done by using third table (the table that has all the dates) and using it as left join to table1.

I have seen some tutorials regarding sequence generation that is by using integers table as:
----------
|integers|
----------
|i |
----
|0 |
|1 |
|2 |
:
:
|9 |
We know we can generate sequence of numbers using integers table using the concept of cross joins.
How this concept can be used in my case ie generating all dates between two date range. or any other good method will do too.
Note: the two date range is not fixed as user has option to select any date.
Waiting for the right solutions.
Thank You.
User avatar
sergio-pro
Forum Commoner
Posts: 88
Joined: Sat Dec 27, 2008 12:26 pm

Re: how to fill up the missing dates ??

Post by sergio-pro »

Hi

Here is how you can do it:

in this example there are three additional tables:
day_index - containing days (1-31)
month_index - containing months (1-12)
year_index - containig years you use (eg 1980-2020)
you can actually substitute day_index and month_index with one table year_day_index (1-356) and constructing date with MAKEDATE(YEAR, DAY_OF_THE_YEAR)

Code: Select all

 
SELECT h.`no_of_hits`, dates.date_val
FROM hits h
RIGHT OUTER JOIN
(
  SELECT yi.num `y`, mi.num `m`, di.num `d`,
  DATE(CONCAT_WS('-', yi.num, mi.num, di.num)) date_val
  FROM year_index yi, month_index mi, day_index di
  WHERE yi.num BETWEEN 2008 AND 2009
    AND mi.num BETWEEN 1 AND 12
    AND di.`num` BETWEEN 1 AND 31
    AND DATE(CONCAT_WS('-', yi.num, mi.num, di.num))  IS NOT NULL
    ORDER BY y, m, d
) as dates
ON h.`visit_date` = dates.date_val
WHERE dates.date_val BETWEEN DATE('2008-02-21') AND DATE('2009-02-02')
 
Ask if you need more details on this query
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Re: how to fill up the missing dates ??

Post by PHPycho »

Thanks a lot Mr. sergio-pro!!
That solution was really awesome. Hats off to you :-)
Post Reply