代码之家 › 专栏 › 技术社区 › Kishore Mohan

使用Postgres获取每小时的总利用时间

psql postgresql sql

Kishore Mohan · 技术社区 · 7 年前

输入:

-------------------------------------------
start_time           |  end_time           
-------------------------------------------
2019-03-28 04:00:00  |  2019-03-28 04:15:00
2019-03-28 04:00:00  |  2019-03-28 04:30:00
2019-03-28 04:40:00  |  2019-03-28 04:50:00
2019-03-28 04:50:00  |  2019-03-28 05:15:00
2019-03-28 05:15:00  |  2019-03-28 05:45:00
2019-03-28 06:15:00  |  2019-03-28 06:45:00
2019-03-28 07:00:00  |  2019-03-28 08:00:00
2019-03-29 04:00:00  |  2019-03-29 04:15:00
2019-03-29 04:00:00  |  2019-03-29 04:30:00
2019-03-29 04:40:00  |  2019-03-29 04:50:00
2019-03-29 04:50:00  |  2019-03-29 05:15:00
2019-03-29 05:15:00  |  2019-03-29 05:45:00
2019-03-29 06:15:00  |  2019-03-29 06:45:00
2019-03-29 07:00:00  |  2019-03-29 08:00:00

我想得到跨日期每小时的总利用时间。持续时间也可以在相同的持续时间上重叠。

预期输出:

------------------------------------------------------------
duration                                 |  utilised_time
------------------------------------------------------------
2019-03-28 12:00 - 2019-03-28 01:00      |    0
2019-03-28 01:00 - 2019-03-28 02:00      |    0
2019-03-28 02:00 - 2019-03-28 03:00      |    0
2019-03-28 03:00 - 2019-03-28 04:00      |    0
2019-03-28 04:00 - 2019-03-28 05:00      |    50
2019-03-28 05:00 - 2019-03-28 06:00      |    45
2019-03-28 06:00 - 2019-03-28 07:00      |    45
2019-03-28 07:00 - 2019-03-28 08:00      |    60
2019-03-28 08:00 - 2019-03-28 09:00      |    0
2019-03-29 12:00 - 2019-03-29 01:00      |    0
2019-03-29 01:00 - 2019-03-29 02:00      |    0
2019-03-29 02:00 - 2019-03-29 03:00      |    0
2019-03-29 03:00 - 2019-03-29 04:00      |    0
2019-03-29 04:00 - 2019-03-29 05:00      |    50
2019-03-29 05:00 - 2019-03-29 06:00      |    45
2019-03-29 06:00 - 2019-03-29 07:00      |    45
2019-03-29 07:00 - 2019-03-29 08:00      |    60
2019-03-29 08:00 - 2019-03-29 09:00      |    0

我们如何使用PSQL实现这一点?

1 回复 | 直到 7 年前

Gordon Linoff 7 年前

您可以使用生成小时数 generate_series() . 然后使用 left join 和 aggregation :

select g.ts, g.ts + interval '1 hour',
       sum(extract(epoch from (least(g.ts + interval '1 hour', i.end_time) -
                               greatest(g.ts, i.start_time)
                              )
                   )
          ) / 60 as duration_in_minutes
from generate_series('2019-03-28 12:00:00'::timestamp, '2019-03-29 08:00 - 2019-03-29 09:00:00'::timestamp, interval '1 hour'
                    ) g(ts) left join
       input i
       on i.start_time < g.ts + interval '1 hour' and
          i.end_time > g.ts
group by g.ts
order by g.ts;

你的结果和你的问题都不清楚你想要什么时间。您明确表示“我想获得跨日期每小时的总利用时间。”但是,您的示例结果并非每小时都有。您可以使用 where 条款