CODEWITHSUNDEEP
mysql
user001
user001

Reputation: 991

How to get the previous day records from mysql table?

I am trying to fetch previous day records from table but I am not finding how to do it exactly. Need your help please..

Table: RECORD_DATA

id       creationDate    
1   | 2013-05-03 04:03:35 |    
2   | 2013-05-03 04:03:35 |

Now I need to get all the records that were created on 2013-05-03. Time can be anything. So my query should have LIKE operator.

I am using below query and it gives me empty set.

select creationDate from RECORD_DATA 
where creationDate LIKE DATE_SUB(STR_TO_DATE('2012-04-05','%d/%m/%Y'),INTERVAL 1 DAY);

Upvotes: 7

Views: 30674

Answers (5)

Greg
Greg

Reputation: 31

The answers here seem to be answering 'How do I find something within the past 24 hours?', not 'How do I find something from the previous calendar date?'. Different questions.

If you need to find something from the previous calendar date, you can try:

select creationDate from RECORD_DATA 
where date(creationDate) = curdate() - INTERVAL 1 DAY;

No need to convert dates to strings and do string comparisons.

Upvotes: 3

Isao
Isao

Reputation: 25

You can use 

SELECT *
FROM `tableName`
WHERE DAY(created) = DAY(CURRENT_DATE - INTERVAL 1 DAY)

Upvotes: 1

JMK
JMK

Reputation: 28059

I think this should do it:

SELECT * FROM RECORD_DATA WHERE `creationDate` >= CURDATE()-1 and `creationDate` < CURDATE();

Upvotes: 1

Aida Paul
Aida Paul

Reputation: 2722

Fairly simple when done with SQL, just add the condition

WHERE creationDate BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE()

There is no need to convert creationDate as it is already a date :). And i belive that this will be the fastest way to check it (which will matter if you go over large data sets).

Upvotes: 25

John Woo
John Woo

Reputation: 263693

This will use INDEX on creationDate (if there is any).

SELECT  *
FROM    TableName
WHERE   creationDate >= CURDATE() - INTERVAL 1 DAY AND
        creationDate < CURDATE()

Upvotes: 1

Related Questions