Postgres 13

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?.

12 rows affected

5 rows affected

id	employee_id	activity_type	created_at
1	1	1	2020-11-18 07:10:25+00
2	2	1	2020-11-18 07:30:25+00
3	3	1	2020-11-18 07:50:25+00
4	2	2	2020-11-18 19:10:25+00
5	3	2	2020-11-18 19:22:38+00
6	1	2	2020-11-18 20:01:05+00
7	1	1	2020-11-19 07:11:23+00
8	1	2	2020-11-19 16:21:53+00
9	1	1	2020-11-19 19:11:23+00
10	1	2	2020-11-19 20:21:53+00
11	1	1	2020-11-20 07:41:38+00
12	1	2	2020-11-20 08:52:01+00

id	name	division_id
1	John	1
2	Amber	2
3	Mike	1
4	Jimmy	1
5	Kathy	2

SELECT 
     d::date AT TIME ZONE 'Asia/Kuala_Lumpur' AS created_date,  
     e.id,  
     e.name,
     e.division_id,
     ARRAY_AGG(
       a.id
     ) as rows,             
     MIN(a.created_at) FILTER (WHERE a.activity_type = 1) as min_time_in,
     MAX(a.created_at) FILTER (WHERE a.activity_type = 2) as max_time_out,
     ARRAY_AGG(
       CASE
           WHEN a.activity_type = 1
           THEN a.created_at
           ELSE NULL
       END
     ) as check_ins,
     ARRAY_AGG(
       CASE
           WHEN a.activity_type = 2
           THEN a.created_at
           ELSE NULL
       END
     ) as check_outs        
FROM    (SELECT MIN(created_at), MAX(created_at) FROM attendance) AS r(startdate,enddate)
  , generate_series(
        startdate::timestamp AT TIME ZONE 'Asia/Kuala_Lumpur', 
        enddate::timestamp AT TIME ZONE 'Asia/Kuala_Lumpur', 
        interval '1 day') g(d)
    CROSS JOIN  employee e
    LEFT JOIN   attendance a ON a.created_at::date = d::date AND e.id = a.employee_id
    where created_at >= timestamp '2020-11-20' AT TIME ZONE 'Asia/Kuala_Lumpur' and created_at < timestamp '2020-11-20' AT TIME ZONE 'Asia/Kuala_Lumpur' + interval '1 day'
    and division_id = 1
GROUP BY 
    created_date
  , e.id
  , e.name
  , e.division_id
ORDER BY 
    created_date
  , e.id;

created_date	id	name	division_id	rows	min_time_in	max_time_out	check_ins	check_outs
2020-11-19 08:00:00	1	John	1	{8,9,10}	2020-11-19 19:11:23+00	2020-11-19 20:21:53+00	{NULL,"2020-11-19 19:11:23+00",NULL}	{"2020-11-19 16:21:53+00",NULL,"2020-11-19 20:21:53+00"}