mysql: get record count between two date-time

Question

I am stuck with a problem in MySQL. I want to get the count of records between two date-time entries.
For example:
I have a column in my table named 'created' having the datetime data type.

I want to count records which were created date-time between "TODAY'S 4:30 AM" and "CURRENT DATE TIME".

I tried some of MySQL's functions but still no luck with it.

Can you please help me with this? thanks.

score 96 · Accepted Answer · edited May 12 '17 at 08:15

96

May be with:

SELECT count(*) FROM `table` 
where 
    created_at>='2011-03-17 06:42:10' and created_at<='2011-03-17 07:42:50';

or use between:

SELECT count(*) FROM `table` 
where 
    created_at between '2011-03-17 06:42:10' and '2011-03-17 07:42:50';

You can change the datetime as per your need. May be use curdate() or now() to get the desired dates.

edited May 12 '17 at 08:15

answered Apr 26 '11 at 06:20

Harry Joy

58,650
30
162
207

14

Please note that order matters, you need to first write smaller date time and then bigger date time – Hafiz Jun 27 '12 at 10:51
What if date and time on separate column. – Satanand Tiwari Oct 28 '17 at 08:46
In that case, you will need to a combination of date and time conditions. For example, if you want to filter for some time in a single day then date column will have an equal condition along with time having between condition. In other cases, you can just filter with between in date and order by time get all records of in those dates. – Harry Joy Oct 30 '17 at 07:05
1

What happens if we don't specify the time in the query, like: **SELECT count(*) FROM `table` where created_at between '2011-03-10' and '2011-03-17';** ? Does MySQL automatically consider the time as: **00:00:00** ? – shasi kanth Apr 19 '18 at 13:15

score 6 · Answer 2 · answered Apr 26 '11 at 06:19

6

select * from yourtable where created < now() and created > '2011-04-25 04:00:00'

answered Apr 26 '11 at 06:19

Wes

6,455
3
22
26

score 5 · Answer 3 · answered Apr 26 '11 at 06:25

5

select * from yourtable 
   where created < now() 
     and created > concat(curdate(),' 4:30:00 AM')

answered Apr 26 '11 at 06:25

Shakti Singh

84,385
21
134
153

score 1 · Answer 4 · answered Oct 21 '19 at 10:58

1

for speed you can do this

WHERE date(created_at) ='2019-10-21'

answered Oct 21 '19 at 10:58

Aditya Tomar

841
1
13
20

mysql: get record count between two date-time

4 Answers4

Linked

Related