List:General Discussion« Previous MessageNext Message »
From:Daniel Kasak Date:August 17 2005 4:11am
Subject:Re: SELECT question - query for records over a series of dates
View as plain text  
Dan Tappin wrote:

> I have a table full of data... a log of sorts.  Each row has a  
> timestamp.
>
> I want to generate some reports based on this data.
>
> For example I want a COUNT(*) of the rows for each day for the past  
> week, 30 days, 12 months etc.
>
> I have no problem generating the query but I am stuck on a creative  
> way to deal with the periods with no data.  For example:
>
> SELECT COUNT(*) as hits,
> DATE(date_impression) as date
> FROM
> table
> GROUP BY date
>
> +-----------------------+---------------------+
> |hits                   |date                 |
> +-----------------------+---------------------+
> |                    39 | 2005-08-12          |
> |                    27 | 2005-08-13          |
> |                    38 | 2005-08-15          |
> |                    28 | 2005-08-16          |
> +-----------------------+---------------------+
>
> Now the problem is that there could be days with no data (the 14th in  
> my example).  Ideally I want to show the last 7 days (or what ever  
> period I want) and show the COUNT(*) including the days with no data  
> like...
>
> +-----------------------+---------------------+
> |hits                   |date                 |
> +-----------------------+---------------------+
> |                    39 | 2005-08-12          |
> |                    27 | 2005-08-13          |
> |                     0 | 2005-08-14          |
> |                    38 | 2005-08-15          |
> |                    28 | 2005-08-16          |
> +-----------------------+---------------------+
>
> Now I can manipulate the data afterwards (i.e. look for empty rows  
> via PHP where this is going to end up...) but it would be much easier  
> to get the data direct from MySQL.  Is there any (easy) way to do  
> this in MySQL?
>
> Dan T
>
Not that I know of.

When I have to do things like this, I write a script which does a 
separate query per day. Unfortunately PHP's date functions are pretty 
horrible, so I also use mysql as a calculator to find the next day in my 
loop. This is terribly inefficient, but very easy, and our server is 
high powered and under no load :) Maybe someone has a better idea though.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped
website: http://www.nusconsulting.com.au
Thread
SELECT question - query for records over a series of datesDan Tappin17 Aug
  • Re: SELECT question - query for records over a series of datesDaniel Kasak17 Aug
  • Re: SELECT question - query for records over a series of datesMichael Stassen17 Aug
    • Re: SELECT question - query for records over a series of datesDan Tappin17 Aug
      • Re: SELECT question - query for records over a series of datesMichael Stassen18 Aug