CODEWITHSUNDEEP
mysqldatedatetime
user984621
user984621

Reputation: 48521

MySQL - select data from database between two dates

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.

Upvotes: 22

Views: 141875

Answers (8)

Koushik Das
Koushik Das

Reputation: 10813

You can use MySQL DATE function like below

For instance, if you want results between 2017-09-05 till 2017-09-09

SELECT DATE(timestamp_field) as date FROM stocks_annc WHERE DATE(timestamp_field) >= '2017-09-05' AND DATE(timestamp_field) <= '2017-09-09'

Make sure to wrap the dates within single quotation ''

Edit: A better solution would be this. It would make sure that it uses the index if any exists.

select date(timestamp_field) as date from stocks_annc where time_stamp_field >= '2022-01-01 00:00:00' and time_stamp_field <= '2022-01-10 00:00:00'

Hope this helps.

Upvotes: 0

Brian Hoover
Brian Hoover

Reputation: 7991

Searching for created_at <= '2011-12-06' will search for any records that where created at or before midnight on 2011-12-06 . You want to search for created_at < '2011-12-07'.

Upvotes: 1

user7618441
user7618441

Reputation: 41

Another alternative is to use DATE() function on the left hand operand as shown below

SELECT users.* FROM users WHERE DATE(created_at) BETWEEN '2011-12-01' AND '2011-12-06'

 

Upvotes: 4

Loki Astari
Loki Astari

Reputation: 264729

You need to use '2011-12-07' as the end point as a date without a time default to time 00:00:00.

So what you have actually written is interpreted as:

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

And your time stamp is: 2011-12-06 10:45:36 which is not between those points.
Change this too:

 SELECT users.* 
 FROM   users
 WHERE  created_at >= '2011-12-01'  -- Implied 00:00:00
   AND  created_at <  '2011-12-07'  -- Implied 00:00:00 and smaller than 
                                   --                  thus any time on 06

Upvotes: 7

Marek Př&#237;hoda
Marek Př&#237;hoda

Reputation: 11198

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

Upvotes: 9

dash
dash

Reputation: 91550

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));

Upvotes: 53

vextorspace
vextorspace

Reputation: 934

Have you tried before and after rather than >= and <=? Also, is this a date or a timestamp?

Upvotes: 1

sys_debug
sys_debug

Reputation: 4003

Maybe use in between better. It worked for me to get range then filter it

Upvotes: 0

Related Questions