如何将时间戳日期与 MySQL 中的仅日期参数进行比较？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10483123/
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 compare timestamp dates with date-only parameter in MySQL?
提问by Michael
In a SQL statement, how do I compare a date saved as TIMESTAMP with a date in YYYY-MM-DD format?
在 SQL 语句中，如何将保存为 TIMESTAMP 的日期与 YYYY-MM-DD 格式的日期进行比较？
Ex.: SELECT * FROM table WHERE timestamp = '2012-05-05'
前任。： SELECT * FROM table WHERE timestamp = '2012-05-05'
I want this query returns all rows having timestamp in the specified day, but it returns only rows having midnight timestamp.
我希望此查询返回在指定日期具有时间戳的所有行，但它仅返回具有午夜时间戳的行。
thanks
谢谢
回答by Marcus Adams
You can use the DATE()function to extract the date portion of the timestamp:
您可以使用该DATE()函数来提取时间戳的日期部分：
SELECT * FROM table
WHERE DATE(timestamp) = '2012-05-05'
Though, if you have an index on the timestamp column, this would be faster because it could utilize the index:
但是，如果您在时间戳列上有索引，这会更快，因为它可以利用索引：
SELECT * FROM table
WHERE timestamp BETWEEN '2012-05-05 00:00:00' AND '2012-05-05 23:59:59'
回答by juergen d
WHERE cast(timestamp as date) = '2012-05-05'
回答by Tom Kitson
As suggested by some, by using DATE(timestamp)you are applying manipulation to the column and therefore you cannot rely on the index ordering.
正如某些人所建议的那样，通过使用DATE(timestamp)您正在对列应用操作，因此您不能依赖索引排序。
However, using BETWEENwould only be reliable if you include the milliseconds. In the example timestamp BETWEEN '2012-05-05 00:00:00' AND '2012-05-05 23:59:59'you exclude records with a timestamp between 2012-05-05 23:59:59.001and 2012-05-05 23:59:59.999. However, even this method has some problems, because of the datatypes precision. Occasionally 999 milliseconds is rounded up.
但是，BETWEEN只有在包含毫秒的情况下，使用才会可靠。在示例时间戳中，BETWEEN '2012-05-05 00:00:00' AND '2012-05-05 23:59:59'您排除了时间戳介于2012-05-05 23:59:59.001和之间的记录2012-05-05 23:59:59.999。然而，由于数据类型的精度，即使这种方法也有一些问题。有时会四舍五入 999 毫秒。
The best thing to do is:
最好的做法是：
SELECT * FROM table
WHERE date>='2012-05-05' AND date<'2012-05-06'
回答by Cfreak
SELECT * FROM table WHERE timestamp >= '2012-05-05 00:00:00'
AND timestamp <= '2012-05-05 23:59:59'
回答by adrien
Use a conversion function of MYSQL :
使用 MYSQL 的转换函数：
SELECT * FROM table WHERE DATE(timestamp) = '2012-05-05'
This should work
这应该工作
回答by asnyder
As I was researching this I thought it would be nice to modify the BETWEEN solution to show an example for a particular non-static/string date, but rather a variable date, or today's such as CURRENT_DATE(). This WILL use the indexon the log_timestamp column.
当我研究这个时，我认为修改 BETWEEN 解决方案以显示特定非静态/字符串日期的示例会很好，而是一个可变日期，或者今天的CURRENT_DATE(). 这将使用log_timestamp 列上的索引。
SELECT *
FROM some_table
WHERE
log_timestamp
BETWEEN
timestamp(CURRENT_DATE())
AND # Adds 23.9999999 HRS of seconds to the current date
timestamp(DATE_ADD(CURRENT_DATE(), INTERVAL '86399.999999' SECOND_MICROSECOND));
I did the seconds/microseconds to avoid the 12AM case on the next day. However, you could also do `INTERVAL '1 DAY' via comparison operators for a more reader-friendly non-BETWEEN approach:
我做了秒/微秒以避免第二天凌晨 12 点的情况。但是，您也可以通过比较运算符执行 `INTERVAL '1 DAY' 以获得更友好的非 BETWEEN 方法：
SELECT *
FROM some_table
WHERE
log_timestamp >= timestamp(CURRENT_DATE()) AND
log_timestamp < timestamp(DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY));
Both of these approaches will use the index and should perform MUCH faster. Both seem to be equally as fast.
这两种方法都将使用索引并且应该执行得更快。两者似乎同样快。
回答by devesh
In case you are using SQL parameters to run the query then this would be helpful
如果您使用 SQL 参数来运行查询，那么这会有所帮助
SELECT * FROM table WHERE timestamp between concat(date(?), ' ', '00:00:00') and concat(date(?), ' ', '23:59:59')
回答by Muhammad Fahad
Use
用
SELECT * FROM table WHERE DATE(2012-05-05 00:00:00) = '2012-05-05'
回答by KeyMaker00
When I read your question, I thought your were on Oracle DBuntil I saw the tag 'MySQL'. Anyway, for people working with Oracle here is the way:
当我读到你的问题时，我以为你在Oracle DB 上，直到我看到标签“MySQL”。无论如何，对于使用 Oracle 的人来说，这里是这样的：
SELECT *
FROM table
where timestamp = to_timestamp('21.08.2017 09:31:57', 'dd-mm-yyyy hh24:mi:ss');
