mysql - 按一天中的小时搜索时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4865753/
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 - search timestamp by hour of day
提问by themerlinproject
I have a column named updatetimethat is a timestamp. So, for example, an average looking value could be: 2011-02-01 09:00:51. I want to be able to search through and return all results for a particular hour of the day regardless of the date.
我有一个名为列updatetime是一个timestamp。因此，例如，平均外观值可能是：2011-02-01 09:00:51。我希望能够搜索并返回一天中特定时间的所有结果，而不管日期如何。
For example if I searched the column for values BETWEEN 09:00:00 AND 09:59:99it would return:
例如，如果我在列中搜索BETWEEN 09:00:00 AND 09:59:99它会返回的值：
2011-02-01 09:00:51
2011-01-31 09:20:51
2011-01-11 09:55:44
etc....
SELECT * FROM table WHERE updatetime ......
SELECT * FROM table WHERE updatetime ......
Thoughts?
想法？
回答by Piskvor left the building
You could use the HOUR()function:
您可以使用HOUR()函数：
SELECT * FROM 'table' WHERE HOUR(`updatetime`) = 9
Alas, this query's performance will be horrible, as soon as you go over a few thousand rows - functions aren't indexable, so there will be a full table scan each time this query runs.
唉，这个查询的性能会很糟糕，一旦你超过几千行 - 函数是不可索引的，所以每次运行这个查询时都会有一次全表扫描。
What we did in a similar situation: we created another column updatetime_hour, indexed it, and populated it on insert (and updated on update); then the query becomes fast:
我们在类似情况下做了什么：我们创建了另一个列updatetime_hour，索引它，并在插入时填充它（并在更新时更新）；然后查询变得很快：
SELECT * FROM 'table' WHERE `updatetime_hour` = 9
Yes, we have denormalized the data, and it's a bit more housekeeping, but I have yet to see a faster solution. (We considered and measured insert and update triggers to populate the updatetime_hourfrom updatetime, but decided against for performance; see if they would be useful for you.)
是的，我们对数据进行了非规范化处理，这需要更多的内务处理，但我还没有看到更快的解决方案。（我们考虑并测量了插入和更新触发器来填充updatetime_hourfrom updatetime，但决定不考虑性能；看看它们是否对您有用。）
回答by Arnaud Le Blanc
回答by diagonalbatman
SELECT * FROM table WHERE hour('updatetime') = 9;
Would return 9am..
早上9点回来。。
回答by DTest
Possibility:
SELECT * FROM 'table' WHERE HOUR(updatetime)='09';
可能性：
SELECT * FROM 'table' WHERE HOUR(updatetime)='09';
回答by emir
These answers do not work, this is what worked for me only
这些答案不起作用，这仅对我有用
SELECT hour(FROM_UNIXTIME(`updatetime`)) AS date_formatted FROM `table` where hour(FROM_UNIXTIME(`updatetime`))='9'
