Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table attendance (id int, u_id int, swipe_time timestamp, status text);
insert into attendance
values
(1, 1, '2020-08-20 16:00:00', 'IN'),
(2, 1, '2020-08-20 20:00:00', 'OUT'),
(3, 1, '2020-08-20 21:00:00', 'IN'),
(4, 1, '2020-08-21 01:00:00', 'OUT'),
(5, 1, '2020-08-21 16:00:00', 'IN'),
(6, 1, '2020-08-21 19:00:00', 'OUT');

CREATE TABLE



INSERT 0 6


create table users (id int, c_id int, u_name text, status text, emp_id int);
insert into users
values
(1, 1, 'XXXXXXXX', 'Active', 1),
(2, 1, 'YYYYYYYY', 'Active', 2)
;

CREATE TABLE



INSERT 0 2


with inout as (
  select ain.u_id, 
         ain.swipe_time as time_in,
         (select min(aout.swipe_time)
          from attendance aout
          where aout.u_id = ain.u_id
            and aout.status = 'OUT'
            and aout.swipe_time > ain.swipe_time) as time_out
  from attendance ain
  where ain.status = 'IN'
), expanded as (
  select u_id, 
         time_in::date as "date", 
         time_in,
         time_out
  from inout     
  where time_in::date = time_out::date  
  union all
  select i.u_id, 
         x.time_in::date as date, 
         x.time_in,
         x.time_out
  from inout i   
    cross join lateral (
       select i.u_id, 
              i.time_in, 
              i.time_in::date + 1 as time_out
       union all
       select i.u_id, 
              i.time_out::date, 
              i.time_out
    ) x
  where i.time_out::date > i.time_in::date  
)
select u.id,
       u.u_name,
       e."date", 
       min(e.time_in) as time_in,
       max(e.time_out) as time_out,
       sum(e.time_out - e.time_in) as duration
from users u 
  left join expanded e on u.id = e.u_id
group by u.id, u.u_name, e."date"
order by u.id, e."date";


id
u_name
date
time_in
time_out
duration


1
XXXXXXXX
2020-08-20
2020-08-20 16:00:00
2020-08-21 00:00:00
07:00:00

1
XXXXXXXX
2020-08-21
2020-08-21 00:00:00
2020-08-21 19:00:00
04:00:00

2
YYYYYYYY
null
null
null
null



SELECT 3

id	u_name	date	time_in	time_out	duration
1	XXXXXXXX	2020-08-20	2020-08-20 16:00:00	2020-08-21 00:00:00	07:00:00
1	XXXXXXXX	2020-08-21	2020-08-21 00:00:00	2020-08-21 19:00:00	04:00:00
2	YYYYYYYY	null	null	null	null