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

CREATE TABLE employee (
  id           int PRIMARY KEY  -- !
, name         text             -- do NOT use char(n) !
, division_id  int
);

INSERT INTO employee VALUES
(1,	'John',	        1),
(2,	'Amber',	2),
(3,	'Mike',	        1),
(4,	'Jimmy',	1),
(5,	'Kathy',	2);

CREATE  TABLE attendance (
  id             int PRIMARY KEY  --!
, employee_id    int NOT NULL REFERENCES employee -- FK!
, activity_type  int
, created_at     timestamptz NOT NULL
);

INSERT INTO attendance VALUES
(1,	1,	1,'2020-11-18 07:10:25 +00:00'),
(2,	2,	1,'2020-11-18 07:30:25 +00:00'),
(3,	3,	1,'2020-11-18 07:50:25 +00:00'),
(4,	2,	2,'2020-11-18 19:10:25 +00:00'),
(5,	3,	2,'2020-11-18 19:22:38 +00:00'),
(6,	1,	2,'2020-11-18 20:01:05 +00:00'),
(7,	1,	1,'2020-11-19 07:11:23 +00:00'),
(8,	1,	2,'2020-11-19 16:21:53 +00:00'),
(9,	1,	1,'2020-11-19 19:11:23 +00:00'),
(10,	1,	2,'2020-11-19 20:21:53 +00:00'),
(11,	1,	1,'2020-11-20 07:41:38 +00:00'),
(12,	1,	2,'2020-11-20 08:52:01 +00:00');

TABLE attendance;
TABLE employee;
5 rows affected

12 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


-- time zones fixed (among other things):
SELECT *
FROM  (        -- complete employee/date grid for division in range
   SELECT g.d::date AS the_date, id AS employee_id, name, division_id
   FROM  (
      SELECT generate_series(MIN(created_at) AT TIME ZONE 'Asia/Kuala_Lumpur'
                           , MAX(created_at) AT TIME ZONE 'Asia/Kuala_Lumpur'
                           , interval '1 day'
                         )
      FROM   attendance
      ) g(d)
   CROSS  JOIN employee e
   WHERE  e.division_id = 1
   ) de
LEFT   JOIN (  -- checkins & checkouts per employee/date for division in range
   SELECT employee_id, ts::date AS the_date
        , array_agg(id) as rows
        , min(ts)             FILTER (WHERE activity_type = 1) AS min_check_in
        , max(ts)             FILTER (WHERE activity_type = 2) AS max_check_out
        , array_agg(ts::time) FILTER (WHERE activity_type = 1) AS check_ins
        , array_agg(ts::time) FILTER (WHERE activity_type = 2) AS check_outs
   FROM  (
      SELECT a.id, a.employee_id, a.activity_type, a.created_at AT TIME ZONE 'Asia/Kuala_Lumpur' AS ts  -- convert to timestamp
      FROM   employee   e
      JOIN   attendance a ON a.employee_id = e.id
   -- WHERE  a.created_at >= timestamp '2020-11-20' AT TIME ZONE 'Asia/Kuala_Lumpur' -- "sargable" expressions
   -- AND    a.created_at <  timestamp '2020-11-21' AT TIME ZONE 'Asia/Kuala_Lumpur' -- exclusive upper bound (includes all of 2020-11-20);
      AND    e.division_id = 1
      ORDER  BY a.employee_id, a.created_at, a.activity_type  -- optional to guarantee sorted arrays
   ) sub
   GROUP  BY 1, 2
   ) a USING (the_date, employee_id)
ORDER  BY 1, 2;


the_date
employee_id
name
division_id
rows
min_check_in
max_check_out
check_ins
check_outs


2020-11-18
1
John
1
{1}
2020-11-18 15:10:25
null
{15:10:25}
null

2020-11-18
3
Mike
1
{3}
2020-11-18 15:50:25
null
{15:50:25}
null

2020-11-18
4
Jimmy
1
null
null
null
null
null

2020-11-19
1
John
1
{6,7}
2020-11-19 15:11:23
2020-11-19 04:01:05
{15:11:23}
{04:01:05}

2020-11-19
3
Mike
1
{5}
null
2020-11-19 03:22:38
null
{03:22:38}

2020-11-19
4
Jimmy
1
null
null
null
null
null

2020-11-20
1
John
1
{8,9,10,11,12}
2020-11-20 03:11:23
2020-11-20 16:52:01
{03:11:23,15:41:38}
{00:21:53,04:21:53,16:52:01}

2020-11-20
3
Mike
1
null
null
null
null
null

2020-11-20
4
Jimmy
1
null
null
null
null
null


-- original
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"}

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

the_date	employee_id	name	division_id	rows	min_check_in	max_check_out	check_ins	check_outs
2020-11-18	1	John	1	{1}	2020-11-18 15:10:25	null	{15:10:25}	null
2020-11-18	3	Mike	1	{3}	2020-11-18 15:50:25	null	{15:50:25}	null
2020-11-18	4	Jimmy	1	null	null	null	null	null
2020-11-19	1	John	1	{6,7}	2020-11-19 15:11:23	2020-11-19 04:01:05	{15:11:23}	{04:01:05}
2020-11-19	3	Mike	1	{5}	null	2020-11-19 03:22:38	null	{03:22:38}
2020-11-19	4	Jimmy	1	null	null	null	null	null
2020-11-20	1	John	1	{8,9,10,11,12}	2020-11-20 03:11:23	2020-11-20 16:52:01	{03:11:23,15:41:38}	{00:21:53,04:21:53,16:52:01}
2020-11-20	3	Mike	1	null	null	null	null	null
2020-11-20	4	Jimmy	1	null	null	null	null	null