给定开始时间、结束时间和秒。获取特定小时内的秒数

Question

我正在使用 Vertica 数据库。我试图从以下示例会话数据中获取特定小时内的总秒数。任何示例 SQL 代码都会非常有帮助 - 感谢

         start time        end time           session length(secs) 
 2010-02-21 20:30:00      2010-02-21 23:30:00    10800
 2010-02-21 21:30:00     2010-02-21 22:30:00     3600
 2010-02-21 21:45:00      2010-02-21 21:59:00      840
 2010-02-21 22:00:00     2010-02-21 22:20:00     1200
 2010-02-21 22:30:00      2010-02-21 23:30:00     3600

所需的输出

hour   secs_in_that_hour
20         1800
21         6240
22         8400
23         3600

Answer 1

您需要一个包含每小时的表，以便您可以将其加入。该联接将基于开始时间和结束时间之间的小时，然后您可以使用 (min(hour end,end time) - max 提取时间（开始时间，开始时间））。然后按小时和总和分组。

由于我不了解vertica，所以我对此没有完整的答案。

Answer 2

Vertica 基于 PostgresSQL，特别是在语言方面。您可以做的最好的事情就是查找 Postgres 的日期时间函数和相关教程。我还没有发现 Postgres 时间函数在 Vertica 中不起作用的实例。

http://www.postgresql.org/docs/8.0/interactive/ functions-datetime.html

您可能可以使用 datediff 类型的函数。 （抱歉，我没时间查。）

Answer 3

请参见 Vertica 函数

TIMESERIES 子句

提供间隙填充和插值 (GFI) 计算，这是时间序列分析计算的重要组成部分。有关详细信息和示例，请参阅《程序员指南》中的“使用时间序列分析”。

句法

TIMESERIES slice_time AS 'length_and_time_unit_expression' OVER (
... [ window_partition_clause (page 147) [ , ... ] ]
... ORDER BY time_expression )
... [ ORDER BY table_column [ , ... ] ]

Answer 4

最简单的方法是仅提取间隔（时间戳之间的差异）上的纪元（秒数）。

至于重叠的金额，您需要首先按小时进行细分。其中一些时间不存在，因此您需要使用 TIMESERIES 子句。

这个想法是首先创建每小时的时间片，然后 theta 加入来查找（并扇出）所有可能的匹配。这基本上是在寻找时间范围的所有重叠部分。幸运的是，这非常简单，因为它就在开始时间在切片结束之前并且结束时间大于切片开始的任何地方。

然后，您使用最大和最小来找到切片内开始和停止的实际时间，将它们减去，将间隔转换为秒并完成。

请参阅下面的示例。

with slices as ( 
  select slice_time slice_time_start, slice_time + interval '1 hour' slice_time_end
  from (
    select min(start_time) time_range from mytest
    union all
    select max(end_time) from mytest
  ) range
  timeseries slice_time as '1 HOUR' over (order by range.time_range)
)
select slice_time_start "hour", extract(epoch from sum( least(end_time, slice_time_end)-greatest(slice_time_start, start_time))) secs_in_that_hour
from slices join mytest on ( start_time < slice_time_end and end_time > slice_time_start)
group by 1
order by 1

如果您的数据不太干净，可能会出现一些边缘情况，或者需要额外的过滤。