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.

score 50 · Accepted Answer · answered Dec 10 '11 at 17:18

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

If anyone else has problem with the last example, try the following syntax: `SELECT users.* from users WHERE created_at BETWEEN '2011-12-01' AND date_add('2011-12-01', INTERVAL 7 DAY);` — Leia, Jan 06 '20 at 22:44

score 9 · Answer 2 · answered Dec 10 '11 at 17:19

9

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

answered Dec 10 '11 at 17:19

Marek Příhoda

11,108
3
39
53

Martin York · Answer 3 · 2011-12-10T17:26:07.847

7

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

edited Dec 10 '11 at 17:26

answered Dec 10 '11 at 17:17

Martin York

257,169
86
333
562

+1 I think we posted the same answer at exactly the same time :-) – dash Dec 10 '11 at 17:19
@dash Actually Loki posted it half a minute earlier and his was supposed to be the accepted answer, but you were luckier. +1 for him for the faster answer, and +1 for you for giving him +1. – Racil Hilan Mar 24 '14 at 14:19

score 4 · Answer 4 · edited Feb 24 '17 at 20:19

4

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'

edited Feb 24 '17 at 20:19

manotheshark

4,297
17
30

answered Feb 24 '17 at 17:43

user7618441

41
1

score 1 · Answer 5 · answered Dec 10 '11 at 17:14

1

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

answered Dec 10 '11 at 17:14

vextorspace

934
2
10
25

my bad, before and after are from something else. for a timestamp you need to add a time as well as date as others have answered. – vextorspace Dec 10 '11 at 17:23

score 1 · Answer 6 · edited Mar 31 '17 at 10:55

1

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'.

edited Mar 31 '17 at 10:55

Nawin

1,653
2
14
23

answered Dec 10 '11 at 17:16

Brian Hoover

7,861
2
28
41

Koushik Das · Answer 7 · 2022-09-30T07:13:41.610

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.

score 0 · Answer 8 · answered Dec 10 '11 at 17:14

0

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

answered Dec 10 '11 at 17:14

sys_debug

3,883
17
67
98

MySQL - select data from database between two dates

8 Answers8

Linked

Related