CODEWITHSUNDEEP
sqlpostgresql
user39950
user39950

Reputation: 473

How to query for time not covered

I have a table 'sessions' like this:

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"

now I want to query the (sum of) time within the actual day of the timestamps (day starts at min(start and ends at (max end) ) where the time is NOT covered by sessions.

so for 2018-05-17 the result would be

11 hours total - 3 hours covered = 8 hours idle

Upvotes: 1

Views: 64

Answers (1)

klin
klin

Reputation: 121784

total is the difference max(end_t)- min(start_t) and covered is the 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)

Upvotes: 1

Related Questions