MySQL Datetime Query Help

Esteemed MySQL Legends,

I have a bunch of timestamps in a datetime type column like this: "2005-11-12 21:15:27"

I have about 10K rows and want to know how many are in each hour of the day. I.e. how many between 21:00 and 22:00 like the example above.

Is there any way of doing this with a simple query that doesn't involve PHP then parsing the results?

Something like SELECT COUNT(time_stamp) FROM table WHERE time_stamp BETWEEN "hour" 21 AND "hour" 22

Any help would be greatly appreciated!

PS Can't do sub-queries since it's MySQL 3.X

 

Not sure if this will work but :
You can do something like
SELECT count(*)
FROM table
where date_part('hour', timestamp) in (10, 11)

Edit - not sure if it works with MySQL

 

Thanks but it's not liking it. From which Db Engine did you get that?

 

That's usually for PostgreSQL but MySQL has a similar finction ... the search continues

 

Looks like MySQL only does year() from what I can see.

EDIT: Scratch that: http://www.databasejournal.com/features/mysql/article.php/2172731

Code in that link works:

SELECT count( * )
FROM table
WHERE HOUR( timestamp ) = 11

PHP:

Ironically almost exactly as my initial pseudo code in post 1

 

I was just about to suggest another way with substr but I see that you did it

 

lol, that's almost as old as I am - time for an upgrade perhaps?

 

Not until RHEL 3 supports all of it in RPMs.

And I've never had anything I couldn't do with MySQL 3 - it just takes a few more lines of code every now and then.