Query between 2 dates hours & minutes

Question

I have a problem with a mysql query, and can't seem to find anything related.

I have a Date column in my table, called "date". The format is "YYYY-MM-DD HH:MM:SS".

I am trying to count all data over several days, but only when the date is between 08:30:00 and 18:30:00.

I know how to use HOUR(date), but I don't know how to integrate the minutes in the query.

I've been thinking of using cases, but I'm not too sure about it.

Any help would be great.

Best regards, Cheskq.

Edit with the query I've been doing:

select count(*), YEAR(date), MONTH(date), DAY(date)
from table
where YEAR(date) = 2014
AND HOUR(date) between 08 and 19
group by YEAR(date), MONTH(date), DAY(date);

Answer 1

I would suggest subtracting 30 minutes for the hour comparison:

select count(*), YEAR(date), MONTH(date), DAY(date)
from table
where YEAR(date) = 2014 AND
      HOUR(date - interval 30 minute) between 08 and 17
group by YEAR(date), MONTH(date), DAY(date);

Note that the upper limit is 17, assuming that the interval ends just before 18:30:00 (at, say, 18:29:59.999).

You can also express this as:

select count(*), YEAR(date), MONTH(date), DAY(date)
from table
where YEAR(date) = 2014 AND
      TIME(date) BETWEEN TIME('08:30:00') and TIME('18:30:00')
group by YEAR(date), MONTH(date), DAY(date);