Postgres 16

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

set datestyle = 'MDY';  -- to accomodate local datestyle

CREATE TABLE tbl (employee_id int, ts timestamp, TYPE text);
INSERT INTO tbl VALUES
  (504, '04/03/2015 01:05:02 PM', 'Login')
, (504, '04/03/2015 03:26:16 PM', 'logout')
, (504, '04/03/2015 03:28:52 PM', 'Login')
, (504, '04/03/2015 03:29:04 PM', 'Login')
, (504, '04/03/2015 03:30:25 PM', 'logout')
, (504, '04/03/2015 03:31:41 PM', 'Login')
, (504, '04/03/2015 05:40:19 PM', 'logout')
, (505, '04/03/2015 05:43:40 PM', 'Login')
, (505, '04/03/2015 08:17:59 PM', 'logout')
, (505, '04/03/2015 10:07:59 PM', 'Login')
, (505, '04/03/2015 10:12:59 PM', 'Login')
, (505, '04/03/2015 11:30:59 PM', 'logout')
/*  -- removed duplicates in test case
, (504, '04/04/2015 03:31:41 PM', 'Login')
, (504, '04/04/2015 05:40:19 PM', 'logout')
, (505, '04/04/2015 05:43:40 PM', 'Login')
, (505, '04/04/2015 10:07:59 PM', 'logout')
*/
;

SET



CREATE TABLE



INSERT 0 12


SELECT employee_id, ts::date
     , max(ts) FILTER (WHERE type = 'Login')  AS login_time
     , max(ts) FILTER (WHERE type = 'logout') AS logout_time
FROM  (
   SELECT *, count(*) FILTER (WHERE type = 'logout') OVER (PARTITION BY employee_id, ts::date ORDER BY ts DESC) AS grp
   FROM   tbl
   ) sub
GROUP  BY employee_id, ts::date, grp
ORDER  BY employee_id, ts::date, login_time;


employee_id
ts
login_time
logout_time


504
2015-04-03
2015-04-03 13:05:02
2015-04-03 15:26:16

504
2015-04-03
2015-04-03 15:29:04
2015-04-03 15:30:25

504
2015-04-03
2015-04-03 15:31:41
2015-04-03 17:40:19

505
2015-04-03
2015-04-03 17:43:40
2015-04-03 20:17:59

505
2015-04-03
2015-04-03 22:12:59
2015-04-03 23:30:59



SELECT 5


-- to mirror your input date style
SELECT employee_id, ts::date
     , to_char(max(ts) FILTER (WHERE type = 'Login') , 'MM/DD/YYYY HH:MI:SS AM') AS login_time
     , to_char(max(ts) FILTER (WHERE type = 'logout'), 'MM/DD/YYYY HH:MI:SS AM') AS logout_time
FROM  (
   SELECT *, count(*) FILTER (WHERE type = 'logout') OVER (PARTITION BY employee_id, ts::date ORDER BY ts DESC) AS grp
   FROM   tbl
   ) sub
GROUP  BY employee_id, ts::date, grp
ORDER  BY employee_id, ts::date, login_time;


employee_id
ts
login_time
logout_time


504
2015-04-03
04/03/2015 01:05:02 PM
04/03/2015 03:26:16 PM

504
2015-04-03
04/03/2015 03:29:04 PM
04/03/2015 03:30:25 PM

504
2015-04-03
04/03/2015 03:31:41 PM
04/03/2015 05:40:19 PM

505
2015-04-03
04/03/2015 05:43:40 PM
04/03/2015 08:17:59 PM

505
2015-04-03
04/03/2015 10:12:59 PM
04/03/2015 11:30:59 PM



SELECT 5

employee_id	ts	login_time	logout_time
504	2015-04-03	2015-04-03 13:05:02	2015-04-03 15:26:16
504	2015-04-03	2015-04-03 15:29:04	2015-04-03 15:30:25
504	2015-04-03	2015-04-03 15:31:41	2015-04-03 17:40:19
505	2015-04-03	2015-04-03 17:43:40	2015-04-03 20:17:59
505	2015-04-03	2015-04-03 22:12:59	2015-04-03 23:30:59

employee_id	ts	login_time	logout_time
504	2015-04-03	04/03/2015 01:05:02 PM	04/03/2015 03:26:16 PM
504	2015-04-03	04/03/2015 03:29:04 PM	04/03/2015 03:30:25 PM
504	2015-04-03	04/03/2015 03:31:41 PM	04/03/2015 05:40:19 PM
505	2015-04-03	04/03/2015 05:43:40 PM	04/03/2015 08:17:59 PM
505	2015-04-03	04/03/2015 10:12:59 PM	04/03/2015 11:30:59 PM