Postgres 13

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

CREATE TABLE table1 (
  "id" INTEGER,
  "created_at" timestamp,
  "tt" VARCHAR(1),
  "vv" INTEGER
);

INSERT INTO table1
  ("id", "created_at", "tt", "vv")
VALUES
  ('1', '2021-05-08 16:00:01.384687+00', 'A', '100'),
  ('2', '2021-05-08 16:00:03.435737+00', 'B', '50'),
  ('3', '2021-05-08 17:01:00.388957+00', 'A', '101'),
  ('4', '2021-05-08 17:02:00.43814+00', 'B', '49'),
  ('5', '2021-05-08 18:00:30.685336+00', 'A', '102'),
  ('6', '2021-05-08 18:00:40.731571+00', 'B', '48'),
  ('7', '2021-05-08 19:01:02.566949+00', 'A', '103'),
  ('8', '2021-05-08 19:02:00.576785+00', 'B', '47'),
  ('9', '2021-05-08 20:03:10.566949+00', 'A', '104'),
  ('10', '2021-05-08 20:05:00.576785+00', 'B', '46');
10 rows affected

WITH CTE AS (
SELECT 
"id", "created_at", "tt", "vv",
	ROW_NUMBER () OVER (
		PARTITION BY "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")
,EXTRACT(day from  "created_at")
,trunc(EXTRACT(hour from  "created_at") / 2)
		ORDER BY
			"created_at"
	) as rown
	FROM table1)
SELECT "id", date_trunc('hour',"created_at"), "tt", "vv"
FROM CTE 
WHERE CTE.rown = 1
ORDER BY "id"


id
date_trunc
tt
vv


1
2021-05-08 16:00:00
A
100

2
2021-05-08 16:00:00
B
50

5
2021-05-08 18:00:00
A
102

6
2021-05-08 18:00:00
B
48

9
2021-05-08 20:00:00
A
104

10
2021-05-08 20:00:00
B
46


SELECT 
"id", "created_at", "tt", "vv",
	ROW_NUMBER () OVER (
		PARTITION BY "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")
,EXTRACT(day from  "created_at")
,trunc(EXTRACT(hour from  "created_at") / 2)
		ORDER BY
			"created_at"
	) as rown
	FROM table1


id
created_at
tt
vv
rown


1
2021-05-08 16:00:01.384687
A
100
1

3
2021-05-08 17:01:00.388957
A
101
2

5
2021-05-08 18:00:30.685336
A
102
1

7
2021-05-08 19:01:02.566949
A
103
2

9
2021-05-08 20:03:10.566949
A
104
1

2
2021-05-08 16:00:03.435737
B
50
1

4
2021-05-08 17:02:00.43814
B
49
2

6
2021-05-08 18:00:40.731571
B
48
1

8
2021-05-08 19:02:00.576785
B
47
2

10
2021-05-08 20:05:00.576785
B
46
1


SELECT 
MIN("id"), MIN("created_at"), "tt", SUM("vv")

FROM table1 
group by "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")
,EXTRACT(day from  "created_at")
,trunc(EXTRACT(hour from  "created_at") / 2) 


min
min
tt
sum


5
2021-05-08 18:00:30.685336
A
205

1
2021-05-08 16:00:01.384687
A
201

10
2021-05-08 20:05:00.576785
B
46

6
2021-05-08 18:00:40.731571
B
95

2
2021-05-08 16:00:03.435737
B
99

9
2021-05-08 20:03:10.566949
A
104


SELECT 
MIN("id"), MIN("created_at"), "tt", SUM("vv")

FROM table1 
group by "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")
,EXTRACT(day from  "created_at")
,trunc(EXTRACT(hour from  "created_at") / 4) 


min
min
tt
sum


1
2021-05-08 16:00:01.384687
A
406

9
2021-05-08 20:03:10.566949
A
104

10
2021-05-08 20:05:00.576785
B
46

2
2021-05-08 16:00:03.435737
B
194


;SELECT 
MIN("id"), MIN("created_at"), "tt", SUM("vv")

FROM table1 
group by "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")
,EXTRACT(day from  "created_at")


min
min
tt
sum


1
2021-05-08 16:00:01.384687
A
510

2
2021-05-08 16:00:03.435737
B
240


;SELECT 
MIN("id"), MIN("created_at"), "tt", SUM("vv")

FROM table1 
group by "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")
,EXTRACT(week from "created_at") 


min
min
tt
sum


1
2021-05-08 16:00:01.384687
A
510

2
2021-05-08 16:00:03.435737
B
240


;SELECT 
MIN("id"), MIN("created_at"), "tt", SUM("vv")

FROM table1 
group by "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")


min
min
tt
sum


2
2021-05-08 16:00:03.435737
B
240

1
2021-05-08 16:00:01.384687
A
510

id	date_trunc	tt	vv
1	2021-05-08 16:00:00	A	100
2	2021-05-08 16:00:00	B	50
5	2021-05-08 18:00:00	A	102
6	2021-05-08 18:00:00	B	48
9	2021-05-08 20:00:00	A	104
10	2021-05-08 20:00:00	B	46

id	created_at	tt	vv	rown
1	2021-05-08 16:00:01.384687	A	100	1
3	2021-05-08 17:01:00.388957	A	101	2
5	2021-05-08 18:00:30.685336	A	102	1
7	2021-05-08 19:01:02.566949	A	103	2
9	2021-05-08 20:03:10.566949	A	104	1
2	2021-05-08 16:00:03.435737	B	50	1
4	2021-05-08 17:02:00.43814	B	49	2
6	2021-05-08 18:00:40.731571	B	48	1
8	2021-05-08 19:02:00.576785	B	47	2
10	2021-05-08 20:05:00.576785	B	46	1