Problem with mysql date query

Question

mysql> desc persondb;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(25) | YES  |     | NULL    |       |
| lname  | varchar(25) | YES  |     | NULL    |       |
| mydate | datetime    | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from persondb;
+------------+--------+---------------------+
| name       | lname  | mydate              |
+------------+--------+---------------------+
| vishwanath | dalvi  | 2011-08-21 14:50:37 |
| ishwar     | dalvi  | 2011-08-21 14:50:58 |
| ganesh     | kamble | 2011-08-31 14:50:37 |
+------------+--------+---------------------+
3 rows in set (0.00 sec)

mysql> select name,mydate from persondb
    -> where date_sub(now(),INTERVAL 5 DAY)<=mydate;
+------------+---------------------+
| name       | mydate              |
+------------+---------------------+
| vishwanath | 2011-08-21 14:50:37 |
| ishwar     | 2011-08-21 14:50:58 |
| ganesh     | 2011-08-31 14:50:37 |
+------------+---------------------+
3 rows in set (0.00 sec)

I'm using the following query to retrieve the records that matched interval of last 5 days but it is showing the entry of future date which is ganesh | 2011-08-31 14:50:37

  mysql> select name,mydate from persondb
      -> where date_sub(now(),INTERVAL 5 DAY)<=mydate;

Can anybody tell me the reason behind this ?

keyword INTERVAL doesnt mean range in query but amount of time (to do the calculus with) — Imre L
– Imre L, Commented Aug 22, 2011 at 12:42

Jacob · Accepted Answer · 2011-08-22 12:23:59Z

4

SELECT name,mydate FROM persondb
WHERE mydate BETWEEN DATE_SUB(NOW(),INTERVAL 5 DAY) AND NOW();

The reason your query shows dates in the future is because you check if mydate is later or equal to 5 days ago. The date 2011-08-31 14:50:37 fullfils this requirement. You can use BETWEEN to check if it is in the range between now and 5 days ago.

answered Aug 22, 2011 at 12:23

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

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

4 Comments

Imre L Over a year ago

for original question author: between is more readable form and equal to mydate >= DATE_SUB(NOW(),INTERVAL 5 DAY) AND mydate <= NOW()

Jacob Over a year ago

@Viswanathan in your question's query, yes.

Vishwanath Dalvi Over a year ago

where mydate BETWEEN now() and date_sub(now(),INTERVAL 5 DAY); if if put now() first then it returns empty why so ?

Jacob Over a year ago

Because "BETWEEN ... AND ..." is defined to have the MIN value first and the MAX value second. expr BETWEEN min AND max Have a look at the MySQL Docs

Collectives™ on Stack Overflow

Problem with mysql date query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related