如何使用mysql测试日期和日期时间

按照Pascal Martin的说明,您可以从日期+时间字段中提取日期部分:

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

Source: MySQL - Date and Time Functions

Stack Overflow: How does one create an index on the date part of DATETIME field in MySql )

你的日期是“2009-12-19”(或类似的,取决于日期),这被解释为“2009-12-19 00:00:00”。

解决方案是这样比较:

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

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

如果你不想计算下一个日期,你可以使用 adddate 功能:

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

select *
from table
where date >= '$date'
    and date < adddate('$date', interval 1 day)
order by score desc

您可能希望在选择数据时对其进行格式化:

SELECT *, DATE_FORMAT(date, '%Y-%m-%d') AS dateformat FROM table
WHERE dateformat = '$date' ORDER BY score DESC