[RESOLVED] Selecting today and yeterday from date column

fr600

Hi,

I need to select today's and yesterday's entries separately from date column. Date is in this format: 2006-07-28 10:10:26. I have done it once before but now I forgot. The thing is I want one page to display all todays entries and another page to display yesterdays.

I've tried like this:

for today:

SELECT * FROM table WHERE addeddatetime = CURDATE()

for yesterday:

SELECT * FROM table WHERE addeddatetime = (CURDATE() + INTERVAL -1 DAY)

But these doesn't work. I'd really appreciate it if anyone can help me with is.

BTW, I'm using: MySQL 5.1 and PHP 5.1

cahva

Hours, minutes and seconds are in the way. Use DATE() function to get rid of those:
DATE(addeddatetime) = CURDATE()
and the other one:
DATE(addeddatetime) = (CURDATE() + INTERVAL -1 DAY)

fr600

Thanks a lot! That works except that:

"CURDATE() + INTERVAL -1 DAY" shows today

and

"CURDATE() + INTERVAL -2 DAY" shows yesterday

and

"CURDATE()" shows nothing... 😕

cahva

Ok I didnt check that you used interval little bit of funny way. The proper way:

// Yesterday
SELECT * FROM table WHERE DATE(addeddatetime) = (CURDATE() - INTERVAL 1 DAY)

"CURDATE()" shows nothing...

?? What do you mean? Did you forgot to add DATE() to addeddatetime field?

fr600

Actually I meant that:

When I use

SELECT * FROM table WHERE DATE(addeddatetime) = (CURDATE() - INTERVAL 1 DAY)

it shows todays entries and when I use

SELECT * FROM table WHERE DATE(addeddatetime) = (CURDATE() - INTERVAL 2 DAY)

it shows yesterdays entries but when I use

SELECT * FROM table WHERE DATE(addeddatetime) = CURDATE()

it shows nothing...

cahva

Heh.. How can this show todays entries:

SELECT * FROM table WHERE DATE(addeddatetime) = (CURDATE() - INTERVAL 1 DAY)

Clock in the server must be in wrong time! What does it show when you use:

SELECT CURDATE()

fr600

Yes it's the clock thing. Thanks man. I really appreciate your help.