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:

PLAIN TEXT
SQL:
  1. SELECT count( SUBSTRING( `datetime` , 12, 2 ) ) AS `num` , SUBSTRING( `datetime` , 12, 2 ) AS `hour`
  2. FROM `table`
  3. GROUP BY SUBSTRING( `datetime` , 12, 2 )
  4. ORDER BY `num` DESC

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • del.icio.us
  • Netvouz
  • DZone
  • Reddit
  • Furl
  • NewsVine
  • Simpy
  • Slashdot
  • Spurl
  • StumbleUpon
  • YahooMyWeb
  • TailRank
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