MySQL 如何选择具有当天时间戳的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14769026/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select rows that have current day's timestamp?
提问by Hymanie Honson
I am trying to select only today's records from a database table.
我试图从数据库表中只选择今天的记录。
Currently I use
目前我使用
SELECT * FROM `table` WHERE (`timestamp` > DATE_SUB(now(), INTERVAL 1 DAY));
But this takes results for the last 24 hours, and I need it to only select results from today, ignoring the time. How can I select results based on the date only ?
但这需要过去 24 小时的结果，我需要它只选择今天的结果，而忽略时间。如何仅根据日期选择结果？
回答by John Woo
use DATEand CURDATE()
使用DATE和CURDATE()
SELECT * FROM `table` WHERE DATE(`timestamp`) = CURDATE()
I guessusing DATEstill uses INDEX.
我想使用DATE仍然使用 INDEX。
回答by ypercube??
If you want an index to be used and the query not to do a table scan:
如果您希望使用索引并且查询不执行表扫描：
WHERE timestamp >= CURDATE()
AND timestamp < CURDATE() + INTERVAL 1 DAY
To show the difference that this makes on the actual execution plans, we'll test with an SQL-Fiddle(an extremely helpful site):
为了显示这对实际执行计划的影响，我们将使用SQL-Fiddle（一个非常有用的站点）进行测试：
CREATE TABLE test --- simple table
( id INT NOT NULL AUTO_INCREMENT
,`timestamp` datetime --- index timestamp
, data VARCHAR(100) NOT NULL
DEFAULT 'Sample data'
, PRIMARY KEY (id)
, INDEX t_IX (`timestamp`, id)
) ;
INSERT INTO test
(`timestamp`)
VALUES
('2013-02-08 00:01:12'),
--- --- insert about 7k rows
('2013-02-08 20:01:12') ;
Lets try the 2 versions now.
现在让我们尝试 2 个版本。
Version 1 with DATE(timestamp) = ?
版本 1 DATE(timestamp) = ?
EXPLAIN
SELECT * FROM test
WHERE DATE(timestamp) = CURDATE() --- using DATE(timestamp)
ORDER BY timestamp ;
Explain:
解释：
ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF
1 SIMPLE test ALL
ROWS FILTERED EXTRA
6671 100 Using where; Using filesort
It filters all (6671) rowsand then does a filesort (that's not a problem as the returned rows are few)
它过滤所有 (6671) 行，然后进行文件排序（这不是问题，因为返回的行很少）
Version 2 with timestamp <= ? AND timestamp < ?
版本 2 timestamp <= ? AND timestamp < ?
EXPLAIN
SELECT * FROM test
WHERE timestamp >= CURDATE()
AND timestamp < CURDATE() + INTERVAL 1 DAY
ORDER BY timestamp ;
Explain:
解释：
ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF
1 SIMPLE test range t_IX t_IX 9
ROWS FILTERED EXTRA
2 100 Using where
It uses a range scan on the index, and then reads only the corresponding rows from the table.
它对索引使用范围扫描，然后只从表中读取相应的行。
回答by Hamed Persia
SELECT * FROM `table` WHERE timestamp >= CURDATE()
it is shorter , there is no need to use 'AND timestamp < CURDATE() + INTERVAL 1 DAY'
它更短，不需要使用“AND时间戳<CURDATE()+INTERVAL 1 DAY”
because CURDATE() always return current day
因为 CURDATE() 总是返回当天
回答by stefgosselin
How many ways can we skin this cat? Here is yet another variant.
我们有多少种方法可以给这只猫剥皮？这是另一个变体。
SELECT * FROM tableWHERE DATE(FROM_UNIXTIME(timestamp)) = '2015-11-18';
SELECT * FROM tableWHERE DATE(FROM_UNIXTIME( timestamp)) = '2015-11-18';
回答by ViditAgarwal
If you want to compare with a particular date , You can directly write it like :
如果要与特定日期进行比较，可以直接将其编写为：
select * from `table_name` where timestamp >= '2018-07-07';
// here the timestamp is the name of the column having type as timestamp
// 这里的时间戳是类型为时间戳的列的名称
or
或者
For fetching today date , CURDATE() function is available , so :
为了获取今天的日期，CURDATE() 函数可用，所以：
select * from `table_name` where timestamp >= CURDATE();
回答by MarcDefiant
Simply cast it to a date:
只需将其转换为日期：
SELECT * FROM `table` WHERE CAST(`timestamp` TO DATE) == CAST(NOW() TO DATE)
回答by Jerry Jones
This could be the easiest in my opinion:
在我看来，这可能是最简单的：
SELECT * FROM `table` WHERE `timestamp` like concat(CURDATE(),'%');
回答by kochauf
Or you could use the CURRENT_DATE alternative, with the same result:
或者您可以使用 CURRENT_DATE 替代方案，结果相同：
SELECT * FROM yourtable WHERE created >= CURRENT_DATE
回答by Cesar Bourdain Costa
On Visual Studio 2017, using the built-in database for development I had problems with the current given solution, I had to change the code to make it work because it threw the error that DATE() was not a built in function.
在 Visual Studio 2017 上，使用内置数据库进行开发时，我遇到了当前给定解决方案的问题，我不得不更改代码以使其工作，因为它抛出了 DATE() 不是内置函数的错误。
Here is my solution:
这是我的解决方案：
where CAST(TimeCalled AS DATE) = CAST(GETDATE() AS DATE)
where CAST(TimeCalled AS DATE) = CAST(GETDATE() AS DATE)
