How to split records per hour in order to display them as a chart?

Question

Hi, I have an SQL table like this : sales(product,timestamp) I want to display a chart using Open Flash Chart but i don't know how to get the total sales per hour within the last 12 hours. ( the timestamp column is the sale date )

By example i will end up with an array like this : array(12,5,8,6,10,35,7,23,4,5,2,16) every number is the total sales in each hour.

Note: i want to use php or only mysql for this.

Thanks

Answer 1

The SQL is

SELECT HOUR(timestamp), COUNT(product)
FROM sales
ORDER BY HOUR(timestamp)

Loop over the result to get it into an array.

EDIT: Applying requested where condition for unix timestamp

SELECT HOUR(timestamp), COUNT(product)
FROM sales
WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 12 HOUR))
ORDER BY HOUR(timestamp)

Answer 2

Some pseudo code:

foreach timestamp
  use date('G',timestamp) to get hour
  increment array value using the hour as key

Something along those lines. Watch out for timezones.

Answer 3

SELECT HOUR(timestamp),COUNT(*)
FROM sales
WHERE timestamp >= DATE_SUB(NOW(),INTERVAL 12 HOUR)
GROUP BY HOUR(timestamp)