MySQL MySQL时间戳选择日期范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3976837/
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 timestamp select date range
提问by DRKM
Not sure really where to start with this one. Can anyone help/point me in the right direction.
不知道从哪里开始。任何人都可以帮助/指出我正确的方向。
I have a timestamp column in MySQL and I want to select a date range for example, all timestamps which are in Oct 2010.
我在 MySQL 中有一个时间戳列，我想选择一个日期范围，例如 2010 年 10 月的所有时间戳。
Thanks.
谢谢。
回答by Wouter van Nifterick
Usually it would be this:
通常是这样的：
SELECT *
FROM yourtable
WHERE yourtimetimefield>='2010-10-01'
AND yourtimetimefield< '2010-11-01'
But because you have a unix timestamps, you'll need something like this:
但是因为你有一个 unix 时间戳，你需要这样的东西：
SELECT *
FROM yourtable
WHERE yourtimetimefield>=unix_timestamp('2010-10-01')
AND yourtimetimefield< unix_timestamp('2010-11-01')
回答by Cez
A compact, flexible method for timestamps without fractional seconds would be:
没有小数秒的时间戳的紧凑、灵活的方法是：
SELECT * FROM table_name
WHERE field_name
BETWEEN UNIX_TIMESTAMP('2010-10-01') AND UNIX_TIMESTAMP('2010-10-31 23:59:59')
If you are using fractional seconds and a recent version of MySQL then you would be better to take the approach of using the >=and <operators as per Wouter's answer.
如果您使用的是小数秒和 MySQL 的最新版本，那么您最好按照Wouter 的回答采用使用>=and<运算符的方法。
Here is an example of temporal fields defined with fractional second precision (maximum precision in use):
以下是使用小数秒精度（使用的最大精度）定义的时间字段示例：
mysql> create table time_info (t_time time(6), t_datetime datetime(6), t_timestamp timestamp(6), t_short timestamp null);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into time_info set t_time = curtime(6), t_datetime = now(6), t_short = t_datetime;
Query OK, 1 row affected (0.01 sec)
mysql> select * from time_info;
+-----------------+----------------------------+----------------------------+---------------------+
| 22:05:34.378453 | 2016-01-11 22:05:34.378453 | 2016-01-11 22:05:34.378453 | 2016-01-11 22:05:34 |
+-----------------+----------------------------+----------------------------+---------------------+
1 row in set (0.00 sec)
回答by Dan Grossman
SELECT * FROM table WHERE col >= '2010-10-01' AND col <= '2010-10-31'
回答by abhijitcaps
I can see people giving lots of comments on this question. But I think, simple use of LIKEcould be easier to get the data from the table.
我可以看到人们对这个问题发表了很多评论。但我认为，简单的使用LIKE可能更容易从表中获取数据。
SELECT * FROM table WHERE COLUMN LIKE '2013-05-11%'
Use LIKEand post data wild character search. Hopefully this will solve your problem.
使用LIKE和发布数据通配符搜索。希望这将解决您的问题。
回答by Juanma Font
This SQL query will extract the data for you. It is easy and fast.
此 SQL 查询将为您提取数据。它简单快捷。
SELECT *
FROM table_name
WHERE extract( YEAR_MONTH from timestamp)="201010";
回答by Markus Winand
Whenever possible, avoid applying functions to a column in the where clause:
尽可能避免将函数应用于 where 子句中的列：
SELECT *
FROM table_name
WHERE timestamp >= UNIX_TIMESTAMP('2010-10-01 00:00:00')
AND timestamp < UNIX_TIMESTAMP('2010-11-01 00:00:00');
Applying a function to the timestamp column (e.g., FROM_UNIXTIME(timestamp) = ...) makes indexing much harder.
将函数应用于时间戳列（例如，FROM_UNIXTIME(timestamp) = ...）使索引变得更加困难。
回答by Federico
If you have a mysql timestamp, something like 2013-09-29 22:27:10you can do this
如果你有一个 mysql 时间戳，2013-09-29 22:27:10你可以这样做
select * from table WHERE MONTH(FROM_UNIXTIME(UNIX_TIMESTAMP(time)))=9;
Convert to unix, then use the unix time functions to extract the month, in this case 9 for september.
转换为 unix，然后使用 unix 时间函数提取月份，在本例中为 9 为 9 月。
