Dayparting on Datetime Field using Substring

Like this blog? Consider exploring one of our sponsored banner ads...

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

About this entry

You’re currently reading “Dayparting on Datetime Field using Substring,” an entry on BRADINO

Published:
6.10.08 / 1pm
Category:
MySQL
Tags: