CODEWITHSUNDEEP
mysqldategroup-by
user1148875
user1148875

Reputation: 459

How to group mysql query results by hour?

Ill show you what I am doing and then what I ment to.

Now:

SELECT COUNT(*) as `total`, `date` FROM `Events` GROUP BY HOUR(`date`)

This is returning something like that: 

date       | Count
1389105597 | 125

This is what I would like to have, but i dont know how:

 date       | Count
 00:01      | 10
 01:02      | 13
 02:03      | 3
 (...)
 23:00      | 30

Upvotes: 1

Views: 2218

Answers (1)

Marc B
Marc B

Reputation: 360562

Your dates appear to be a unix timestamp - an integer representing the number of seconds since Jan 1,1970. They are NOT a mysql date/time string, which is what most of MySQL's date/time functions expect.

Try the following:

SELECT count(*) AS total, HOUR(FROM_UNIXTIME(`date`)) AS hour
...
GROUP BY HOUR(FROM_UNIXTIME(`date`))

This will convert your timestamp back to a native mysql date/time value, which you can use to extract the hours from.

Upvotes: 3

Related Questions