按日期时间过滤 MYSQL 格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16657202/
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
Filter by datetime MYSQL formatting
提问by Ryan Bauer
I have a Mysql Table that is used for a log file on the that table there is a field called 'log_date' And it stores the date in the following format( %Y-%m-%d %H:%i.%s ).On the DB the dates look like something this 2013-20-05 00:00.00. Lets say today's date is 2013-20-05 And I have log files from 2013-01-01 to present day. If I run a query like this:
我有一个 Mysql 表，用于该表上的日志文件，有一个名为“log_date”的字段，它以以下格式存储日期（%Y-%m-%d %H:%i.%s ). 在 DB 上，日期看起来像 2013-20-05 00:00.00。假设今天的日期是 2013-20-05 我有从 2013-01-01 到现在的日志文件。如果我运行这样的查询：
SELECT * FROM log_table
WHERE STR_TO_DATE(log_date, '%Y-%m-%d %H:%i.%s') < '2013-05-05 00:00.00'
This is returning every row in the DB including rows that are greater than 2013-05-05 00:00.00
这将返回数据库中的每一行，包括大于 2013-05-05 00:00.00 的行
And if I reverse the < (less then) to a > (greater then) with a query that looks like this:
如果我使用如下所示的查询将 <（小于）反转为 >（大于）：
SELECT * FROM log_table
WHERE STR_TO_DATE(log_date, '%Y-%m-%d %H:%i.%s') > '2013-05-05 00:00.00'
Then it returns ZERO rows. I think the time stamp is what is causing the problem I have worked with the date format before but not the DateTime format. Why is this happening?
然后它返回零行。我认为时间戳是导致我之前使用过日期格式但不是 DateTime 格式的问题的原因。为什么会这样？
回答by blotto
log_date should be of DateTime data type. It is much simpler to use MySQL DATE function. Some examples
log_date 应该是 DateTime 数据类型。使用 MySQL DATE 函数要简单得多。一些例子
SELECT * FROM log_table
WHERE DATE(log_date) < '2013-05-05'
SELECT * FROM log_table
WHERE DATE(log_date) > '2013-05-05'
SELECT * FROM log_table
WHERE DATE(log_date) BETWEEN '2013-04-05' AND '2013-05-05'
SELECT * FROM log_table
WHERE DATE(log_date) BETWEEN DATE(CURRENT_DATE() - INTERVAL 2 WEEK) AND
DATE(CURRENT_DATE() + INTERVAL 4 DAY)
回答by Cataclysm
You can try with that..
你可以试试那个..
WHERE DATE_FORMAT(AUCTION_DATE, '%Y%m%d') >= DATE_FORMAT('2013/5/18', '%Y%m%d')
You can also get today date using now()function.
您还可以使用now()函数获取今天的日期。
