MySQL 选择两个日期之间的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6329830/
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
Selecting records between two dates
提问by Itai Sagi
I have the following query:
我有以下查询：
SELECT dm.app_id, apt.app_name, COUNT(dm.app_id)
FROM dm_openapp dm
JOIN app_table apt ON dm.app_id = apt.app_id
GROUP BY dm.app_id
Basically this table also has dates associated to each record, and I need to get a range of all the records between time X and Y, I tried using the following, for example, but to no avail:
基本上这个表也有与每条记录关联的日期，我需要获取时间 X 和 Y 之间所有记录的范围，例如，我尝试使用以下方法，但无济于事：
WHERE dm.dl_time BETWEEN '2011-05-31' AND '2011-05-06'
Any idea as to what to do? the dl_time column is a timestamp type.
知道该怎么做吗？dl_time 列是时间戳类型。
回答by James
It is better to use DATETIME column type for these things. Than this should work: use str_to_date()function. Also, swap the BETWEEN values.
对于这些事情最好使用 DATETIME 列类型。这应该有效：使用str_to_date()函数。此外，交换 BETWEEN 值。
WHERE dm.dl_time BETWEEN str_to_date('2011-05-06','%Y-%m-%d') AND str_to_date('2011-05-31','%Y-%m-%d')
回答by Bohemian
Ummm... you've got the data the wrong way around. BETWEENmust be LOW value to HIGH value:
嗯……你把数据弄错了。BETWEEN必须是低值到高值：
Try this:
尝试这个：
WHERE dm.dl_time BETWEEN '2011-05-06' AND '2011-05-31' -- Note date values swapped
You can ignore the other answers, which also haven't noticed this...
您可以忽略其他答案，这些答案也没有注意到这一点......
回答by maple_shaft
You need to convert the strings in your where clause to a date STR_TO_DATE. Here are a variety of different functions in MySQL that you can use for DATE manipulation.
您需要将 where 子句中的字符串转换为 date STR_TO_DATE。以下是 MySQL 中可用于 DATE 操作的各种不同函数。
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
