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.

Harry Joy · Accepted Answer · 2017-05-12 08:15:24Z

99

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, 2017 at 8:15

user5890979

answered Apr 26, 2011 at 6:20

Harry Joy

59.8k30 gold badges166 silver badges211 bronze badges

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

4 Comments

Hafiz Over a year ago

Please note that order matters, you need to first write smaller date time and then bigger date time

Satanand Tiwari Over a year ago

What if date and time on separate column.

Harry Joy Over a year ago

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.

shasi kanth Over a year ago

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 ?

Wes · Accepted Answer · 2011-04-26 06:19:53Z

6

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

answered Apr 26, 2011 at 6:19

Wes

6,6053 gold badges24 silver badges26 bronze badges

Comments

Shakti Singh · Accepted Answer · 2011-04-26 06:25:10Z

5

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

answered Apr 26, 2011 at 6:25

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

Comments

Aditya Tomar · Accepted Answer · 2019-10-21 10:58:20Z

1

for speed you can do this

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

answered Oct 21, 2019 at 10:58

Aditya Tomar

9221 gold badge13 silver badges20 bronze badges

Collectives™ on Stack Overflow

mysql: get record count between two date-time

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related