MySQL - select data from database between two dates

Question

I have saved the dates of a user's registration as a datetime, so that's for instance 2011-12-06 10:45:36. I have run this query and I expected this item - 2011-12-06 10:45:36 - will be selected:

SELECT `users`.* FROM `users` WHERE created_at >= '2011-12-01' AND
created_at <= '2011-12-06'

But is not. Exist any elegant way, how to select this item? As a first idea that I got was like 2011-12-06 + 1, but this doesn't looks very nice.

Answer 1

Your problem is that the short version of dates uses midnight as the default. So your query is actually:

SELECT users.* FROM users 
WHERE created_at >= '2011-12-01 00:00:00' 
AND created_at <= '2011-12-06 00:00:00'

This is why you aren't seeing the record for 10:45.

Change it to:

SELECT users.* FROM users 
WHERE created_at >= '2011-12-01' 
AND created_at <= '2011-12-07'

You can also use:

SELECT users.* from users 
WHERE created_at >= '2011-12-01' 
AND created_at <= date_add('2011-12-01', INTERVAL 7 DAY)

Which will select all users in the same interval you are looking for.

You might also find the BETWEEN operator more readable:

SELECT users.* from users 
WHERE created_at BETWEEN('2011-12-01', date_add('2011-12-01', INTERVAL 7 DAY));

Answer 2

SELECT users.* FROM users WHERE created_at BETWEEN '2011-12-01' AND '2011-12-07';