I have a table with this structure and data

............................................. and so on

I want the structure in this way

The time is not fixed, so hard to figure out. One logic is to get the data after 7 pm to 8 am but not sure. Any suggestions will be highly appreciated

CodePudding user response：

database structure

create table work (id serial, data text, ev_date date, ev_time time);
insert into work(data, ev_date, ev_time) values 
('sample1', '2022-11-10',   '21:10:00'),
('sample2', '2022-11-10',   '23:50:00'),
('sample3', '2022-11-11',   '01:20:00'),
('sample4', '2022-11-11',   '05:30:00'),
('sample5', '2022-11-11',   '20:59:00'),
('sample6', '2022-11-11',   '23:01:00'),
('sample7', '2022-11-12',   '01:01:00'),
('sample8', '2022-11-12',   '04:01:00');

query

the algorithm is as follows:

1. we group by date
2. we determine the interval -5 hours and 8 hours
3. we look for the first time values depending on the order (-5 hours - the first larger value, 8 hours - the first smaller value)
4. removing the nulls

with s as (select ev_date from work group by ev_date),
     p as (select s.ev_date,
     (select work.ev_time from work 
        where (s.ev_date - interval '5 hour')::date = work.ev_date 
                and ((s.ev_date - interval '5 hour')::time < work.ev_time) 
        order by work.ev_date,work.ev_time limit 1) start_time,
     (select work.ev_time from work 
        where (s.ev_date   interval '8 hour')::date = work.ev_date 
                and ((s.ev_date   interval '8 hour')::time > work.ev_time) 
        order by work.ev_date,work.ev_time desc limit 1) end_time
     from s)
     select p.ev_date,p.start_time,p.end_time from p where p.start_time is not null and p.end_time is not null order by p.ev_date;

result

  ev_date   | start_time | end_time
------------ ------------ ----------
 2022-11-11 | 21:10:00   | 05:30:00
 2022-11-12 | 20:59:00   | 04:01:00