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]
