SQL Server :
SELECT [activity_dt], count(*)
FROM table1
GROUP BY DATEPART(day, [activity_dt]), DATEPART(hour, [activity_dt]);
Oracle :
SELECT [activity_dt], count(*)
FROM table1
GROUP BY TO_CHAR(activity_dt, 'DD'), TO_CHAR(activity_dt, 'hh');
MySQL :
SELECT [activity_dt], count(*)
FROM table1
GROUP BY hour( activity_dt ) , day( activity_dt )
Using MySQL I usually do it that way:
SELECT count( id ), ...
FROM quote_data
GROUP BY date_format( your_date_column, '%Y%m%d%H' )
order by your_date_column desc;
Or in the same idea, if you need to output the date/hour:
SELECT count( id ) , date_format( your_date_column, '%Y-%m-%d %H' ) as my_date
FROM your_table
GROUP BY my_date
order by your_date_column desc;
If you specify an index on your date column, MySQL should be able to use it to speed up things a little.
In my case... with MySQL:
SELECT ... GROUP BY TIMESTAMPADD(HOUR, HOUR(columName), DATE(columName))
SELECT [activity_dt], COUNT(*) as [Count]
FROM
(SELECT dateadd(hh, datediff(hh, '20010101', [activity_dt]), '20010101') as [activity_dt]
FROM table) abc
GROUP BY [activity_dt]
Source: Stackoverflow.com