MYSQL选择这一天的日期

MY query looks like this:

我的查询如下所示:

SELECT COUNT(entryID) 
FROM table 
WHERE date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)

Will this count the rows whose date values are within the day (starting at 12:00; not within 24 hours)? If not, how do I do so?

这会计算日期值在一天内的行(从12:00开始;不在24小时内)吗?如果没有,我该怎么办?

3 个解决方案

#1

The following should be enough to get records within the current day:

以下内容足以在当天获取记录:

SELECT COUNT(entryID) 
FROM table 
WHERE date >= CURDATE()

As Michael notes in the comments, it looks at all records within the last two days in its current form.

正如迈克尔在评论中指出的那样,它会查看过去两天内目前形式的所有记录。

The >= operator is only necessary if date is actually a datetime - if it's just a date type, = should suffice.

仅当日期实际上是日期时间时才需要> =运算符 - 如果它只是日期类型,则=应该足够。

#2

Here's the solution:

这是解决方案:

SELECT COUNT(entryID) FROM table WHERE DATE(date) >= CURDATE()

SELECT COUNT(entryID)FROM表WHERE DATE(date)> = CURDATE()

Since my date column is type DATETIME, I use DATE(date) to just get the date part, not the time part.

由于我的日期列是DATETIME类型,我使用DATE(日期)来获取日期部分,而不是时间部分。

#3

CURDATE() returns a date like '2012-03-30', not a timestamp like '2012-03-30 21:38:17'. The subtraction of one day also returns just a date, not a timestamp. If you want to think of a date as a timestamp think of it as the beginning of that day, meaning a time of '00:00:00'.

CURDATE()返回类似'2012-03-30'的日期,而不是像'2012-03-30 21:38:17'那样的时间戳。减去一天也只返回一个日期,而不是时间戳。如果您想将日期视为时间戳,则将其视为当天的开始,意味着时间为'00:00:00'。

And this is the reason, why this

这就是为什么这个原因

WHERE date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)

and this

WHERE date > CURDATE()

do the same.

照着做。

I have another hint: SELECT COUNT(entryID) and SELECT COUNT(*) give the same result. SELECT COUNT(*) gives the database-machine more posibilities to optimize counting, so COUNT(*) is often (not always) faster than COUNT(field).

我有另一个提示:SELECT COUNT(entryID)和SELECT COUNT(*)给出相同的结果。 SELECT COUNT(*)为数据库机器提供了更多优化计数的能力,因此COUNT(*)通常(并非总是)比COUNT(字段)快。

#1