将timeFrom和timeTo划分为小时间隔

Question

我有以下内容：

TimeFrom                    TimeTo
2015-08-04 11:00:00.000     2015-08-04 14:00:00.000
2015-08-04 18:00:00.000     2015-08-04 20:00:00.000
2015-08-04 21:00:00.000     2015-08-04 23:00:00.000

我想创造一些能给我所有时间间隔的东西。像这样：

TimeFrom                    TimeTo
2015-08-04 11:00:00.000     2015-08-04 12:00:00.000
2015-08-04 12:00:00.000     2015-08-04 13:00:00.000
2015-08-04 13:00:00.000     2015-08-04 14:00:00.000
2015-08-04 18:00:00.000     2015-08-04 19:00:00.000
2015-08-04 19:00:00.000     2015-08-04 20:00:00.000
2015-08-04 21:00:00.000     2015-08-04 22:00:00.000
2015-08-04 22:00:00.000     2015-08-04 23:00:00.000

有没有一种简单的方法可以实现这一点？这看起来应该很常见，但找不到任何与此相关的东西。

我有很多天的记录，差不多一年了。所有单个记录（timeFrom、timeTo）都在同一天内（它们不跨越多天）。

我正在使用PrestoDB

Answer 1

您可以使用数字表：

SELECT DATE_ADD('HOUR', i - 1, TimeFrom) AS TimeFrom, 
       DATE_ADD('HOUR', i, TimeFrom) AS TimeTo
FROM (SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
      UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
      UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
      UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16
      UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
      UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
) AS numbers
INNER JOIN mytable ON numbers.i <= DATE_DIFF('HOUR', TimeFrom, TimeTo)
ORDER BY TimeFrom

注意：以上查询使用此处为PrestoDB记录的DATE_ADD和DATE_DIFF日期和时间函数。

Answer 2

如果没有另一个每小时列出一次的表，我看不出实现这一目标的方法。如果你被限制在一天内，你可以通过使用UNION ALL 在查询中构造它来伪造它

SELECT h.Hour
  FROM ( SELECT 2015-08-04 00:00:00.000 AS Hour
         UNION ALL SELECT 2015-08-04 01:00:00.000    
         UNION ALL SELECT 2015-08-04 02:00:00.000  
         UNION ALL SELECT 2015-08-04 03:00:00.000  
         UNION ALL SELECT 2015-08-04 04:00:00.000  
         UNION ALL SELECT 2015-08-04 05:00:00.000  
         UNION ALL SELECT 2015-08-04 06:00:00.000  
         UNION ALL SELECT 2015-08-04 07:00:00.000  
         UNION ALL SELECT 2015-08-04 08:00:00.000  
         UNION ALL SELECT 2015-08-04 09:00:00.000  
         UNION ALL SELECT 2015-08-04 10:00:00.000  
         UNION ALL SELECT 2015-08-04 11:00:00.000 
         UNION ALL SELECT 2015-08-04 12:00:00.000 
         UNION ALL SELECT 2015-08-04 13:00:00.000 
         UNION ALL SELECT 2015-08-04 14:00:00.000 
         UNION ALL SELECT 2015-08-04 15:00:00.000 
         UNION ALL SELECT 2015-08-04 16:00:00.000 
         UNION ALL SELECT 2015-08-04 17:00:00.000 
         UNION ALL SELECT 2015-08-04 18:00:00.000 
         UNION ALL SELECT 2015-08-04 19:00:00.000 
         UNION ALL SELECT 2015-08-04 20:00:00.000 
         UNION ALL SELECT 2015-08-04 21:00:00.000 
         UNION ALL SELECT 2015-08-04 22:00:00.000 
         UNION ALL SELECT 2015-08-04 23:00:00.000 ) AS h
where exists 
(select id from mytable where h>=TimeFrom and h<=TimeTo)