My task is to extract data grouped by hours. I do this by issuing a command similar to the one below:
SELECT DISTINCT
COUNT(player_id) AS `count`,
YEAR(date_created) AS `year`,
MONTH(date_created) AS `month`,
DAY(date_created) AS `day`,
HOUR(date_created) AS `hour`
FROM `logs`
WHERE
(controller='player') AND
(action='updatehash') AND (date_created >= FROM_UNIXTIME(1317916800)) AND
(date_created <= FROM_UNIXTIME(1318003199))
GROUP BY `year`, `month`, `day`, `hour`
ORDER BY `year` ASC, `month` ASC, `day` ASC, `hour` ASC
Which returns results similar to the one below:
Array
(
[0] => Array
(
[count] => 2
[year] => 2011
[month] => 10
[day] => 7
[hour] => 16
)
[1] => Array
(
[count] => 5
[year] => 2011
[month] => 10
[day] => 7
[hour] => 17
)
[2] => Array
(
[count] => 21
[year] => 2011
[month] => 10
[day] => 7
[hour] => 18
)
[3] => Array
(
[count] => 3
[year] => 2011
[month] => 10
[day] => 7
[hour] => 19
)
)
The result seem to be OK only that I have to have it return results for the last 24 hours including hours that no count values. So, based on the result I posted above, it should return results from Oct 7, 19:00 down to Oct 6, 18:00.
Is this possible?
Thank you everyone! ;)
Short answer, no using this SQL, you are not joining table, so you can not use the "left join" technique. it is because you really don't have any logging record in that period of time.
May be you can either try create FUNCTION approach or insert at least one record each hour using your programming language.