在 MySQL 中获取两个日期字符串之间的数据
我一直在为我正在处理的项目记录 Twitter 数据，日期信息在字符串字段中保存为 Thu, 14 Jul 2011 06:21:48 +0000 。
如何使用 mySQL 选择两个日期之间的数据？我可以获得大于某个值或小于某个值的数据，但不能获取介于两个值之间的数据。
例如，数据是：
Thu, 14 Jul 2011 06:21:48 +0000
Thu, 14 Jul 2011 12:18:21 +0000
Thu, 14 Jul 2011 18:48:00 +0000
Thu, 14 Jul 2011 23:48:02 +0000
Fri, 15 Jul 2011 06:48:10 +0000
Fri, 15 Jul 2011 12:48:00 +0000
Fri, 15 Jul 2011 18:43:32 +0000
Fri, 15 Jul 2011 23:44:08 +0000
Sat, 16 Jul 2011 06:47:08 +0000
Sat, 16 Jul 2011 12:46:49 +0000
Sat, 16 Jul 2011 18:45:41 +0000
Sat, 16 Jul 2011 23:41:27 +0000
我的 SQL 字符串是：
SELECT *
FROM twitter
WHERE SUBSTR(twitter_date, 6, 11) >= '2011-06-15'
AND SUBSTR(twitter_date, 6, 11) <= '2011-06-21'
我也尝试过 BETWEEN 语句，但没有成功。
任何帮助将不胜感激！
I have been recording Twitter data for a project I'm working on the date information is saved as Thu, 14 Jul 2011 06:21:48 +0000 in a string field.
How do I select data that falls between two dated using mySQL? I can get data larger than a value or smaller than a value but not between to values.
The data for example is:
Thu, 14 Jul 2011 06:21:48 +0000
Thu, 14 Jul 2011 12:18:21 +0000
Thu, 14 Jul 2011 18:48:00 +0000
Thu, 14 Jul 2011 23:48:02 +0000
Fri, 15 Jul 2011 06:48:10 +0000
Fri, 15 Jul 2011 12:48:00 +0000
Fri, 15 Jul 2011 18:43:32 +0000
Fri, 15 Jul 2011 23:44:08 +0000
Sat, 16 Jul 2011 06:47:08 +0000
Sat, 16 Jul 2011 12:46:49 +0000
Sat, 16 Jul 2011 18:45:41 +0000
Sat, 16 Jul 2011 23:41:27 +0000
My SQL string is:
SELECT *
FROM twitter
WHERE SUBSTR(twitter_date, 6, 11) >= '2011-06-15'
AND SUBSTR(twitter_date, 6, 11) <= '2011-06-21'
I've tried BETWEEN statements as well but no luck.
Any help will be appreciated!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您不能使用 Between，因为它们是字符串而不是实际的日期字段。如果您知道日期的格式始终相同，则可以执行以下操作：
STR_TO_DATE(字符串，格式）You can't use between because they are strings and not actual date fields. If you know the format of the date will always be the same, you can do the following:
STR_TO_DATE(str,format)您正在将“2011 年 7 月 6 日”与 2011-06-15 进行比较。
应该是
You are comparing "6 Jul 2011" with 2011-06-15.
It should be
您可能想研究一下 MySQL 的
STR_TO_DATE函数。这将为您提供一个日期，并且日期和日期之间应该按预期工作。You may want to look into MySQLs
STR_TO_DATEfunction. This will give you a date and between should work as expected.查询将会失败。您正在比较两个字符串，而不是日期。您需要明确告诉 MySQL 您要将字符串视为日期。由于它是日期信息，因此将其存储在 MySQL 中的日期或日期时间字段类型中，这会省去所有这些麻烦：
强制左侧为正确的日期值将提示 MySQL 它应该对待右侧 -侧面也作为日期。
The query will fail. You're comparing two STRINGS, not dates. You need to tell MySQL explicitly that you want to treat the strings as dates. Since it's date information, store it in a date or datetime field type in MySQL, which saves you all this trouble:
Forcing your left-hand-side to be a proper date value will hint to MySQL that it should treat the right-hand-side as a date as well.
一旦您更改了表以将日期数据保存在适当的 DATETIME 字段中，您可能需要参考我对以下有关日期和过滤它们的问题的回答，这可能会很有用。
例如，在上面的查询/数据中，如果您查询的某一日期没有数据，会发生什么情况？您不会得到该日期的输出，这在绘制图表等时可能看起来很奇怪。如果您使用我在所附问题中描述的技术，您可以获得附加信息以及其他有用的信息，例如星期几、月份名称等。
全选给定日期内的月份跨度，包括具有 0 值的跨度
希望有帮助！
Once you have altered your table to hold the date data in proper DATETIME fields, you may want to refer to my answer to the following question regarding dates and filtering them, which may prove useful.
For example, in your query / data above, what would happen if you had no data for one of the dates you were querying? You'd get no output for that date, which may look odd when charting, etc. If you use the technique I describe in the attached question, you can get that additional information, and other useful stuff like the day of the week, month name, etc.
Select all months within given date span, including the ones with 0 values
Hope that helps!