One of the most puzzling and challenging things to do with SQL can be
to show what's NOT there, as you're trying to do.
Many people opt to do such a report in their favorite scripting
language for this reason, as one can easily increment timestamps by a
given amount and re-do the query. Can be resource intensive to re-do
the queries for each hour or whatever, but it's often pretty easy.
Another option is to create a table used specifically for joining to
get units of time with no corresponding entries in the other table.
You could create a table like so:
CREATE TABLE all_hours (
date_hour DATETIME,
KEY (date_hour) );
then populate it like so, with perl:
$counter = 0;
while $counter < 1000000 {
print "INSERT INTO all_hours (date_hour) VALUES (
DATE_ADD("2000-01-01 00:00:00", INTERVAL $counter HOUR) );"
$counter++;
}
Then you can join on that table.
A million hour entries would be good for 114 years or so. Fewer would
likely give somewhat better performance.
Dan
On 1/10/07, Paul Halliday <[EMAIL PROTECTED]> wrote:
That query doesn't return empty values. Just to clarify what I want as
the result:
My initial query was this,
mysql> select count(*),DATE_FORMAT(timestamp,'%H') AS hour FROM event
WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11
04:00:00' AND sid=1 GROUP BY hour;
+----------+------+
| count(*) | hour |
+----------+------+
| 4 | 04 |
| 5 | 06 |
| 5 | 07 |
| 1 | 08 |
| 7 | 09 |
| 12 | 10 |
| 73 | 12 |
| 31 | 13 |
| 50 | 14 |
+----------+------+
9 rows in set (0.03 sec)
What I am looking for is 0's for every empty result and up to the end
of the day.
Thanks.
On 1/10/07, Brent Baisley <[EMAIL PROTECTED]> wrote:
> You can't join on the result of calculations in the field selection. The
result is not associated with any table. So the problem
> isn't so much with the date_format statement, but that you are joining on
HOURS.hour the timestamp, not HOURS.hour the DATE_FORMAT.
> I would think you would be getting an error when you run your SELECT.
> Your group by can use the result of a calculation. So you may actually have
two problems, since you are grouping on HOURS.hour, the
> timestamp, the 'hour' the alias name for the calculation result.
> I'm not sure why you don't just pull the hour from the timestamp either.
>
> SELECT COUNT(*), HOUR(timestamp) AS hour FOM HOURS
> LEFT JOIN event ON HOURS.hour=HOUR(timestamp)
> WHERE timestamp BETWEEN '2007-01-09 04:00:00'
> AND '2007-01-10 04:00:00' AND sid=2 GROUP BY hour
>
> ----- Original Message -----
> From: "Paul Halliday" <[EMAIL PROTECTED]>
> To: <mysql@lists.mysql.com>
> Sent: Wednesday, January 10, 2007 8:39 AM
> Subject: Strange query.
>
>
> > Hi,
> >
> > I am trying to deal with empty values so that I can graph data over a
> > 24 hour period without gaps.
> >
> > I created a table called HOURS which simply has 0->23 and I am trying
> > to do a join on this to produce the desired results. I think that the
> > DATE_FORMAT in the query is screwing things up.
> >
> > The query looks something like this:
> >
> > SELECT COUNT(*),DATE_FORMAT(timestamp,'%H') AS hour FROM HOURS LEFT
> > JOIN event ON HOURS.hour=hour WHERE timestamp BETWEEN '2007-01-09
> > 04:00:00' AND '2007-01-10 04:00:00' AND sid=2 GROUP BY HOURS.hour;
> >
> > Any help would be appreciated.
> >
> > Thanks.
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
