Postgres 13

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

create table timerecord (
    id int,
    employee_id int,
    company_id int,
    activity_type int,
    created_at timestamp
);

INSERT INTO timerecord VALUES
(1,	1,	1,	1	,'2020-11-18 7:10:25'),
(2,	2,	1,	1	,'2020-11-18 7:30:25'),
(3,	3,	1,	1	,'2020-11-18 7:50:25'),
(4,	2,	1,	2	,'2020-11-18 19:10:25'),
(5,	3,	1,	2	,'2020-11-18 19:22:38'),
(6,	1,	1,	2	,'2020-11-18 20:01:05'),
(7,	1,	1,	1	,'2020-11-19 7:11:23'),
(8,	1,	1,	2	,'2020-11-19 18:21:53');

SELECT * FROM timerecord;
8 rows affected



id
employee_id
company_id
activity_type
created_at


1
1
1
1
2020-11-18 07:10:25

2
2
1
1
2020-11-18 07:30:25

3
3
1
1
2020-11-18 07:50:25

4
2
1
2
2020-11-18 19:10:25

5
3
1
2
2020-11-18 19:22:38

6
1
1
2
2020-11-18 20:01:05

7
1
1
1
2020-11-19 07:11:23

8
1
1
2
2020-11-19 18:21:53


SELECT 
    created_at::date as created_date,
    company_id,
    employee_id,
    MIN(created_at::time) FILTER (WHERE activity_type = 1) as min_time_in,
    MAX(created_at::time) FILTER (WHERE activity_type = 2) as max_time_out
FROM
   timerecord
where date(created_at) = '2020-11-18' and employee_id = 1
GROUP BY created_date, company_id, employee_id
ORDER BY created_date, employee_id


created_date
company_id
employee_id
min_time_in
max_time_out


2020-11-18
1
1
07:10:25
20:01:05


SELECT 
    created_at::date as created_date,
    company_id,
    employee_id,
    MIN(CASE WHEN activity_type = 1 THEN created_at::time END)as min_time_in,
    MAX(CASE WHEN activity_type = 2 THEN created_at::time END) as max_time_out
FROM
   timerecord
where date(created_at) = '2020-11-18' and employee_id = 1
GROUP BY created_date, company_id, employee_id
ORDER BY created_date, employee_id


created_date
company_id
employee_id
min_time_in
max_time_out


2020-11-18
1
1
07:10:25
20:01:05

id	employee_id	company_id	activity_type	created_at
1	1	1	1	2020-11-18 07:10:25
2	2	1	1	2020-11-18 07:30:25
3	3	1	1	2020-11-18 07:50:25
4	2	1	2	2020-11-18 19:10:25
5	3	1	2	2020-11-18 19:22:38
6	1	1	2	2020-11-18 20:01:05
7	1	1	1	2020-11-19 07:11:23
8	1	1	2	2020-11-19 18:21:53