Query Time range between Dates using DATETIME mysql

Question

I have a database table that has fields as such :

TIME(Datetime)        Update_ID
2013-11-25 05:00:14     XC3
2013-11-25 06:00:13     XC4
2013-11-25 06:00:19     XC5
2013-12-25 23:00:14     XC6
2013-12-25 24:00:00     XC7

So assuming i want to find a trend on the updates to know which period of the day has the a particular number of updates, what i initially think of is doing something like this :

SELECT COUNT(TIME) FROM  table WHERE TIME between '06:00:00' and '12:00:00' 

But this doesn't work because i think since the date is not added with the time, a default value for date is added(some date around 1970). If, i add the beginning and enddate in my query, i am afraid it won't give me the results i need.

Solution 1

Use

WHERE HOUR(TIME)...GROUP BY DAY(TIME) 

in case you have more than 1 day

Solution 2

You are correct, the problem is that when you do not specify the date, a default one is added.

You can use the EXTRACT function to extract the time from a date, like this:

SELECT COUNT(*)
FROM  mytable
WHERE EXTRACT(HOUR_SECOND from TIME) between 60000 and 120000

Note that the time portion in the condition is specified in a different format - i.e. as numbers, without colons and quotes.

Demo on SqlFiddle.