4

My table is using a datetime (YYYY-MM-DD HH:MM:SS) and i need to display today's entries.

my code is only : 

SELECT   * 
FROM     table 
WHERE    date = '$date' 
ORDER    BY score DESC

with 

$date = date("Y-m-d");

well, as expected it doesnt work :| you guys have a solution here ?

Svetlozar Angelov
  • 21,214
  • 6
  • 62
  • 67
David 天宇 Wong
  • 3,724
  • 4
  • 35
  • 47

4 Answers4

18

Following from Pascal Martin, you could extract the date part from the date+time field:

SELECT * FROM table WHERE DATE(date) = '2009-12-19'

Source: MySQL - Date and Time Functions

Be aware however, that this query will not use an index on your date+time field, if you will be having one. (Stack Overflow: How does one create an index on the date part of DATETIME field in MySql)

Community
  • 1
  • 1
Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
  • 1
    If I'm not mistaken, with a solution like this one, in which there is a calculation on the "date" column, that calculation/conversion will have to be made on every line of the table -- ruining the chance of using the index that might have been set on the "date" column. – Pascal MARTIN Dec 19 '09 at 23:02
  • @Pascal: Yes, I think so. I've added the clarification in my answer... In the meantime, I am checking how MySQL handles this, for curiosity's sake. – Daniel Vassallo Dec 19 '09 at 23:06
  • @Daniel : OK ;; thanks for the precision :-) And I'm curious about it too, just to know if what I think is actually true or not ^^ – Pascal MARTIN Dec 19 '09 at 23:08
  • @Pascal: Looks like you are right: http://stackoverflow.com/questions/95183/. Your solution is the way to go when you require the use of an index. – Daniel Vassallo Dec 19 '09 at 23:11
  • @Daniel : thanks for the search, the update, and the link :-) – Pascal MARTIN Dec 19 '09 at 23:12
4

Your date is "2009-12-19" (or something like that, depending on the day), which is interpreted as "2009-12-19 00:00:00".

In your database, you probably don't have any date that's exactly equal to that one, by the second : your dates are like "2009-12-19 12:15:32".

A solution is to compare like this :

select *
from table
where date >= '2009-12-19'
    and date < '2009-12-20'

Which will be interpreted as :

select *
from table
where date >= '2009-12-19 00:00:00'
    and date < '2009-12-20  00:00:00'

And, if you don't want to do the math to get the date of the following date, you can use the adddate function :

select *
from table
where date >= '2009-12-19'
    and date < adddate('2009-12-19', interval 1 day)

So, in your case, something like this should do the trick :

select *
from table
where date >= '$date'
    and date < adddate('$date', interval 1 day)
order by score desc
Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
  • isn't there a more beautiful option to write that ? thx for the tips anyway, that would have took some thinking – David 天宇 Wong Dec 19 '09 at 22:59
  • 1
    Hu ; we don't agree on the definition of "beautiful", I suppose -- at least, I don't think this looks "ugly", but, well... ;; an advantage of this solution is that it should use the index you have on the "date" column, if you have any on that one (and maybe you should) -- while a solution that converts the "date" column or does a calculation on it will remove the abolity to use an index. – Pascal MARTIN Dec 19 '09 at 23:01
  • what do you mean by index ? and by beautiful, I find the solution of Daniel Vassallo more clean – David 天宇 Wong Dec 19 '09 at 23:04
  • 1
    Heu... 600 characters won't be enough to explain what an index is, actually ^^ But you can take a look at this page of the manual, as a start : http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html – Pascal MARTIN Dec 19 '09 at 23:07
0

You probably want to format the data when you select it:

SELECT *, DATE_FORMAT(date, '%Y-%m-%d') AS dateformat FROM table
WHERE dateformat = '$date' ORDER BY score DESC
DisgruntledGoat
  • 70,219
  • 68
  • 205
  • 290
0

You are comparing datetime and date expression, that is why its not working. Use Date() method to return the date part from datetime and then do the comparison. WHERE DATE(date) = '$date' should do. You might have to use aliases to handle this name collision.

amrinder
  • 208
  • 1
  • 7