sql - 如何查询未覆盖的时间

Question

我有一个像这样的表“会话”：

id | start                    | end
1  | "2018-05-17 07:51:38.392"| "2018-05-17 08:51:38.392"
2  | "2018-05-17 09:51:38.392"| "2018-05-17 10:51:38.392"
3  | "2018-05-17 17:51:38.392"| "2018-05-17 18:51:38.392"
4  | "2018-05-18 07:51:38.392"| "2018-05-18 17:51:38.392"

现在我想查询时间戳的实际日期内的（总和）时间（天开始于 min(start 并结束于 (max end) ），其中时间不包含在会话中。

所以对于 2018-05-17 结果将是

总共 11 小时 - 3 小时覆盖 = 8 小时空闲

标签： sqlpostgresql

Answer 1

total是区别max(end_t)- min(start_t)，covered是sum(end_t - start_t)：

with my_table(id, start_t, end_t) as (
values
    (1, '2018-05-17 07:51:38.392'::timestamp, '2018-05-17 08:51:38.392'::timestamp),
    (2, '2018-05-17 09:51:38.392', '2018-05-17 10:51:38.392'),
    (3, '2018-05-17 17:51:38.392', '2018-05-17 18:51:38.392'),
    (4, '2018-05-18 07:51:38.392', '2018-05-18 17:51:38.392')
)

select 
    start_t::date as day,
    max(end_t) - min(start_t) as total, 
    sum(end_t - start_t) as covered,
    max(end_t) - min(start_t) - sum(end_t - start_t) as idle
from my_table
group by 1
order by 1

    day     |  total   | covered  |   idle   
------------+----------+----------+----------
 2018-05-17 | 11:00:00 | 03:00:00 | 08:00:00
 2018-05-18 | 10:00:00 | 10:00:00 | 00:00:00
(2 rows)