trying to count mysql data between time1 and time2

Question

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?

possible duplicate of [mysql: get record count between two date-time](http://stackoverflow.com/questions/5786649/mysql-get-record-count-between-two-date-time) — SubSevn, Sep 03 '13 at 18:11

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

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

edited May 23 '17 at 12:05

Community

1
1

answered Sep 03 '13 at 12:53

SubSevn

1,008
2
10
27

score 0 · Answer 2 · answered Sep 03 '13 at 12:56

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

score 0 · Answer 3 · answered Sep 03 '13 at 13:04

0

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'

answered Sep 03 '13 at 13:04

Indra Yadav

600
5
22

trying to count mysql data between time1 and time2

3 Answers3

Linked