mysql-按一天中的小时搜索时间戳 [英] mysql - search timestamp by hour of day

问题描述

我有一个名为updatetime的列，它是一个timestamp.因此，例如，平均外观值可以是:2011-02-01 09:00:51.我希望能够搜索并返回一天中特定时间的所有结果，而不考虑日期.

I have a column named updatetime that 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.

例如，如果我在列中搜索值BETWEEN 09:00:00 AND 09:59:99，它将返回:

For example if I searched the column for values BETWEEN 09:00:00 AND 09:59:99 it would return:

2011-02-01 09:00:51
2011-01-31 09:20:51
2011-01-11 09:55:44
etc....

SELECT * FROM table WHERE updatetime ......

有想法吗?

推荐答案

您可以使用 A，一旦您查询了几千行，该查询的性能将变得糟糕-函数无法建立索引，因此每次运行此查询时都会进行全表扫描.

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.

在类似情况下的操作:我们创建了另一列updatetime_hour，对其进行了索引，并在插入时填充了该列(并在更新时进行了更新)；然后查询变得很快:

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:

SELECT * FROM 'table' WHERE `updatetime_hour` = 9

是的，我们已经对数据进行了规范化处理，而且还需要更多的内务处理，但是我还没有看到更快的解决方案. (我们考虑并测量了插入和更新触发器，以从updatetime填充updatetime_hour，但出于性能考虑而决定；请查看它们是否对您有用.)

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_hour from updatetime, but decided against for performance; see if they would be useful for you.)

这篇关于mysql-按一天中的小时搜索时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！