3

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.

Improve this question
2
  • 1
    WHERE HOUR(TIME)...GROUP BY DAY(TIME) in case you have more than 1 day Commented Dec 28, 2013 at 12:10
  • @Mihai reply as answer and get the credit. It works, thanks ! Commented Dec 28, 2013 at 12:16

2 Answers 2

Reset to default
2

Use

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

in case you have more than 1 day

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Btw, i used only HOUR(TIME) and it returned a number 29110, when i added GROUP BY DAY(TIME), i get 15 rows returned with different values, is it the sum of all these 15 rows that yields the above value ? btw, i am querying from since July, if GROUP BY presents this results based on each day, am i not suppose to have more than 15 rows returned ? Thanks
You get the all the rows with the time condition between '06:00:00' and '12:00:00' for each day in you table.You could add GROUP BY MONTH(TIME),...YEAR(TIME) if you have that need.
thank you. Just an additional question. Is there a better alternative to between ? I think between matches exactly '06:00:00' to '12:00:00' it seems that it skips records that start with '06:00:01'. If there is no record that starts with '06:00:00' it returns zero. I thought the range should give from that starting value ?
WHERE HOUR(TIME)>= '06:00:00' AND HOUR(TIME)<= 12:00:00';
WHERE TIME(TIME) between '06:00:00' and '12:00:00'
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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.