MYSQL - 检索日期之间的时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2045053/
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
MYSQL - Retrieve Timestamps between dates
提问by Jake
All,
全部，
I have a MYSQL table with a column called timestamp. It is of DATETIMEdatatype and has values like "10/1/2009 3:25:08 PM', "10/1/2009 3:30:05 PM', "10/4/2009 3:40:01 PM', etc..
我有一个 MYSQL 表，其中有一列名为时间戳。它是DATETIME数据类型并且具有像“10/1/2009 3:25:08 PM”、“10/1/2009 3:30:05 PM”、“10/4/2009 3:40:01 PM”这样的值，等等..
I want to write a SQL query to select all the values in the timestamp field occuring between two dates.. something like this:
我想编写一个 SQL 查询来选择发生在两个日期之间的时间戳字段中的所有值.. 像这样：
select timestamp from tablename where timestamp >= userStartDate and timestamp <= userEndDate
The userInput dates will not have time portions. Can you please suggest the correct MySQL Query Syntax for this? Thanks
userInput 日期将没有时间部分。您能否为此建议正确的 MySQL 查询语法？谢谢
回答by Quassnoi
SELECT timestamp
FROM tablename
WHERE timestamp >= userStartDate
AND timestamp < userEndDate + INTERVAL 1 DAY
This will select every record having date portion between userStartDateand userEndDate, provided that these fields have type of DATE(without time portion).
这将选择日期部分在userStartDate和之间的每条记录userEndDate，前提是这些字段的类型为DATE（没有时间部分）。
If the start and end dates come as strings, use STR_TO_DATEto convert from any given format:
如果开始日期和结束日期以字符串形式出现，请使用STR_TO_DATE从任何给定格式进行转换：
SELECT timestamp
FROM tablename
WHERE timestamp >= STR_TO_DATE('01/11/2010', '%m/%d/%Y')
AND timestamp < STR_TO_DATE('01/12/2010', '%m/%d/%Y') + INTERVAL 1 DAY
回答by Sampson
SELECT timestamp
FROM myTable
WHERE timestamp BETWEEN startDate AND endDate
For best results when using
BETWEENwith date or time values, you should useCAST()to explicitly convert the values to the desired data type. Examples: If you compare aDATETIMEto twoDATEvalues, convert theDATEvalues toDATETIMEvalues. If you use a string constant such as'2001-1-1'in a comparison to aDATE, cast the string to aDATE.
-http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between
为了在
BETWEEN与日期或时间值一起使用时获得最佳结果，您应该使用CAST()将值显式转换为所需的数据类型。示例：如果将一个值DATETIME与两个DATE值进行比较，请将DATE值转换为DATETIME值。如果'2001-1-1'在与 a 的比较中使用字符串常量DATE，请将字符串强制转换为 aDATE。
-http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between
回答by Yada
The userInput dates will not have timestamps. You can convert user input to timestamp with:
userInput 日期没有时间戳。您可以使用以下命令将用户输入转换为时间戳：
mysql> SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');
-> 1196440219
Your query can become:
您的查询可以变成：
select timestamp from tablename
where timestamp >= UNIX_TIMESTAMP(userStartDate)
and timestamp <= UNIX_TIMESTAMP(userEndDate)
回答by Milk Man
If your looking to query an event between two timestamps, for a calendar or something where the start and end are stored as a timestamp you can use this
如果您想查询两个时间戳之间的事件，对于日历或开始和结束存储为时间戳的内容，您可以使用它
$qry = "SELECT *
FROM `table`
WHERE '$today_start' >= `event_start`
AND '$today_start' <= `event_end`";
