MySQL: getting rows for a given date

Question

I have the following query:

$yesterday=date("Y-m-d",mktime(0,0,0,date("m"),(date("d")-1),date("Y")));
$sql = "SELECT messages FROM user WHERE sent_date='".$yesterday."'";

With it I select all the messages sent to the users yesterday.

In my database sent_date looks like: 2010-12-28 11:55:30

But with $yesterday I get: 2010-12-28

So I get 0 results.

How can I modify my query so I can select all the messages from yesterday no matter the hour?

Thanks a ton

DrColossos · Accepted Answer · 2010-12-29 17:08:12Z

3

You should use MySQL Date functions for that (assuming the column is a datetime).

SELECT messages FROM user WHERE sent_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

or always from 00:00

SELECT messages FROM user WHERE sent_date = DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 1 DAY);

If this is not giving any results, please edit your question with more info. You exact schema, how do you insert data and ensure that there are valid rows inside the table.

edited Dec 29, 2010 at 17:08

answered Dec 29, 2010 at 16:01

DrColossos

13.1k3 gold badges49 silver badges68 bronze badges

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

4 Comments

Don Over a year ago

For reference: dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

DrColossos Over a year ago

Thanks for noting. I've integrated it into the answer.

ADM Over a year ago

I'm trying both but still 0 results .. I'm trying to debug the mysql date functions now ...

DrColossos Over a year ago

See my edit; the date functions are fine. I'm sure there is something wired going on with your data.

extraneon · Accepted Answer · 2010-12-29 15:37:23Z

2

Perhaps by using

$sql = "SELECT messages FROM user WHERE date(sent_date)='".$yesterday."'";

That way you don't use the time in the datetime for comparison.

answered Dec 29, 2010 at 15:37

extraneon

24.1k2 gold badges51 silver badges52 bronze badges

1 Comment

Don Over a year ago

Try @DrColossos's suggestion above and do it all in MySQL instead of setting in PHP first: SELECT messages FROM user WHERE sent_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

Anthony Pegram · Accepted Answer · 2011-12-21 15:50:39Z

1

You could do a BETWEEN and put in a starting and ending time concatenated on the $yesterday...

Actually... what the next post suggests is better if you want the full date.

edited Dec 21, 2011 at 15:50

Anthony Pegram

128k28 gold badges229 silver badges252 bronze badges

answered Dec 29, 2010 at 15:38

Don

1,5705 gold badges22 silver badges39 bronze badges

Collectives™ on Stack Overflow

MySQL: getting rows for a given date

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related