Postgres 13

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

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, 
        enddate::timestamp, 
        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 d::date = date '2020-11-20' 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-20 08:00:00	1	John	1	{11,12}	2020-11-20 07:41:38+00	2020-11-20 08:52:01+00	{"2020-11-20 07:41:38+00",NULL}	{NULL,"2020-11-20 08:52:01+00"}
2020-11-20 08:00:00	3	Mike	1	{NULL}	null	null	{NULL}	{NULL}
2020-11-20 08:00:00	4	Jimmy	1	{NULL}	null	null	{NULL}	{NULL}