Dayparting on Datetime Field using Substring
Today I needed a quick and easy way to count the number of records for each of the 24 hours in a day for records in a mysql database that has a datetime field. I came up with the following query to solve that problem:
SELECT count( SUBSTRING( `datetime` , 12, 2 ) ) AS `num` , SUBSTRING( `datetime` , 12, 2 ) AS `hour`
FROM `table`
GROUP BY SUBSTRING( `datetime` , 12, 2 )
ORDER BY `num` DESC
FROM `table`
GROUP BY SUBSTRING( `datetime` , 12, 2 )
ORDER BY `num` DESC
Home | MySQL | Dayparting on Datetime Field using Substring
You’re currently reading “ Dayparting on Datetime Field using Substring ,” an entry on BRADINO
- Published:
- 6.10.08 / 1pm
- Category:
- MySQL
Have your say