Split Time for each Hour in a day given start and end time-CodePudding

Input: Table time has State and Two timestamps (start and end time) for each.

user  state      start_time                end_time
1     Work    2022-08-15 11:00:38     2022-08-15 14:11:03
1     Break   2022-08-15 14:11:03     2022-08-15 14:25:25
1     Work    2022-08-15 14:25:25     2022-08-15 15:09:10
1     Work    2022-08-15 15:09:10     2022-08-15 15:14:15
1     Break   2022-08-15 15:14:15     2022-08-15 18:07:50
1     Work    2022-08-15 18:07:50     2022-08-15 19:25:31
1     Work    2022-08-15 19:25:31     2022-08-15 19:34:57
1     Work    2022-08-15 19:34:57     2022-08-15 20:10:57
1     Work    2022-08-15 20:10:57

Requirement:Find the total time spent on "work"(in seconds)between each hour. For example: if we need time spent by user working between 7-8PM the output should be 3593 sec

I was able to get this but say for example we want the worktime from 8 - 9 (which is 10 min as per output) , the below code is unable to get that

Code so far:

select user,start_time,
   extract(epoch from (end_time-start_time)) as seconds
   from times
   where  TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') >= '2022-08-15 19:00:00'
   and TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') < '2022-08-15 20:00:00'
   and state = 'Work'
 union all
select user,start_time,
 extract(epoch from ((('2022-08-15 20:00:00')::timestamp)- 
      start_time)) as seconds
 from times
 where  TO_CHAR(start_timestamp, 'YYYY-MM-DD HH24:MI:SS') >= '2022-08-15 19:00:00'
 and TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') >= '2022-08-15 19:00:00'
 and TO_CHAR(start_timestamp, 'YYYY-MM-DD HH24:MI:SS') < '2022-08-15 20:00:00'
 and state = 'Work'

CodePudding user response：

You can use generate series for calculating the interval in seconds,minutes,hours


CREATE TABLE userlogs 
(
    usernm  varchar(300),
    state   varchar(300),
    start_time  timestamp,
    end_time    timestamp
);

INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Work', '2022-08-15 11:00:38.000000  00:00', '2022-08-15 14:11:03.000000  00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Break', '2022-08-15 14:11:03.000000  00:00', '2022-08-15 14:25:25.000000  00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Work', '2022-08-15 14:25:25.000000  00:00', '2022-08-15 15:09:10.000000  00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Break', '2022-08-15 15:09:10.000000  00:00', '2022-08-15 15:14:15.000000  00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Work', '2022-08-15 15:14:15.000000  00:00', '2022-08-15 18:07:50.000000  00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Break', '2022-08-15 18:07:50.000000  00:00', '2022-08-15 19:07:18.000000  00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Work', '2022-08-15 19:07:18.000000  00:00', '2022-08-15 19:25:31.000000  00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Work', '2022-08-15 19:25:31.000000  00:00', '2022-08-15 19:34:57.000000  00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Work', '2022-08-15 19:34:57.000000  00:00', '2022-08-15 20:10:57.000000  00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Logged Out', '2022-08-15 20:10:57.000000  00:00', null);


SELECT usernm, (count(*) * interval '1 min')::text AS work_interval
FROM  (
   SELECT usernm, generate_series (start_time, end_time - interval '1 min', interval '1 min') AS h
   FROM   userlogs where state='Work'
   ) sub
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '20:00'
AND    h::time <= '21:00'  
GROUP  BY 1
ORDER  BY 1;

OUTPUT :

 
usernm  work_interval
user1   00:10:00