按unix时间戳+ 24小时分组

Question

I am writing a reporting program which is using MySQL as a database and php as scripting language.

What i'm trying to do is to group records by time and group them in hours having last 24 hours in the result set.

Current Query

SELECT count(*),time FROM `service_data` GROUP BY time

the query above does not group them by last 24 hours it just groups similar unixtimestamp together

Table Structure

CREATE TABLE IF NOT EXISTS `service_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `service_id` varchar(255) NOT NULL,
  `ip_address` varchar(255) NOT NULL,
  `time` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

In the result set i want 24 records null if no reports in a particular hour.

Like so

| hour |  reports |
|------|----------|
|   1  |    5     |
|   2  |    10    |
|   3  |    12    |
|   4  |    25    |

Any help will be much appreciated!

Thanks!

Answer 1

To get the last 24 hours, convert the time of 1 day ago to a unix timestamp:

UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))

To convert a timestamp back to an hour, first convert it to a time, then use the HOUR function:

HOUR(FROM_UNIXTIME(time))

Combining these, you can do:

SELECT HOUR(FROM_UNIXTIME(time)) AS hour, COUNT(*)
FROM service_data
WHERE time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))
GROUP BY hour

If the table is very large, add an index on the time column so this will not have to do a full scan.

To get results for hours that have no data, you'll need to use a LEFT JOIN with a table that contains all the hour numbers:

SELECT t1.hour, COUNT(s.id) AS reports
FROM (SELECT 0 AS hour UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 ... UNION SELECT 23) AS t1
LEFT JOIN service_data AS s
ON t1.hour = HOUR(FROM_UNIXTIME(s.time)) AND s.time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))
GROUP BY t1.hour
ORDER BY t1.hour