trying to count mysql data between time1 and time2

问题:

I have a database table 'email_log' which would be filled with information as: user, subject and opened (as datetime)

What I need is a new query from which I can see how many rows contain the column 'opened' between 08:00 and 09:00.

What I had didn't work:

SELECT count(*) as count FROM email_log WHERE opened BETWEEN '00:08:00' AND '00:09:00';

Does anyone know the trick?

回答1:

You might need to include the entire format for the datetime - since it's got both a date and a time.

See: mysql: get record count between two date-time

回答2:

try

SELECT count(*) as count FROM email_log WHERE 
opened BETWEEN STR_TO_DATE('5/15/2012', '%c/%e/%Y') 
AND STR_TO_DATE('5/15/2012', '%c/%e/%Y'); // date only

SELECT count(*) as count FROM email_log WHERE 
opened BETWEEN STR_TO_DATE('8:06:26 AM', '%r') 
AND STR_TO_DATE('8:06:26 AM', '%r'); // time only

回答3:

Try this

SELECT COUNT(*) AS COUNT 
FROM email_log 
WHERE  DATE_FORMAT(opened,"%H:%i:%S")  > '00:08:00' AND  DATE_FORMAT(opened,"%H:%i:%S")  < '00:09:00'