Postgres 14

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

Create Table table_name(received_on timestamp, adc_v INT, adc_i INT, acc_axe_x INT, acc_axe_y INT, acc_axe_z INT);
Insert Into table_name Values('2022-07-29 15:52:00',2509,646,0,0,0),
('2022-07-29 15:52:00',2510,636,0,0,0),
('2022-07-29 15:52:00',2510,636,0,0,0),
('2022-07-29 15:52:00',2509,646,0,0,0),
('2022-07-29 15:52:00',2510,624,0,0,0),
('2022-07-29 15:52:01',2509,648,0,0,0),
('2022-07-29 15:52:01',2510,635,0,0,0),
('2022-07-29 15:52:01',2509,647,0,0,0),
('2022-07-29 15:52:01',2510,636,0,0,0),
('2022-07-29 15:52:02',2510,636,0,0,0),
('2022-07-29 15:52:02',2509,646,0,0,0),
('2022-07-29 15:52:02',2510,632,0,0,0),
('2022-07-29 15:52:03',2509,646,0,0,0),
('2022-07-29 15:52:03',2510,636,0,0,0),
('2022-07-29 15:52:03',2510,647,0,0,0),
('2022-07-29 15:52:03',2510,636,0,0,0),
('2022-07-29 15:52:04',2509,647,0,0,0),
('2022-07-29 15:52:04',2509,635,0,0,0),
('2022-07-29 15:52:04',2509,637,0,0,0),
('2022-07-29 15:52:05',2510,637,0,0,0),
('2022-07-29 15:52:05',2510,636,0,0,0),
('2022-07-29 15:52:05',2509,647,0,0,0),
('2022-07-29 15:52:05',2509,636,0,0,0),
('2022-07-29 15:52:06',2510,647,0,0,0),
('2022-07-29 15:52:06',2510,635,0,0,0),
  ('2022-07-29 15:53:00',2510,635,0,0,0);

CREATE TABLE



INSERT 0 26


SELECT* from table_name


received_on
adc_v
adc_i
acc_axe_x
acc_axe_y
acc_axe_z


2022-07-29 15:52:00
2509
646
0
0
0

2022-07-29 15:52:00
2510
636
0
0
0

2022-07-29 15:52:00
2510
636
0
0
0

2022-07-29 15:52:00
2509
646
0
0
0

2022-07-29 15:52:00
2510
624
0
0
0

2022-07-29 15:52:01
2509
648
0
0
0

2022-07-29 15:52:01
2510
635
0
0
0

2022-07-29 15:52:01
2509
647
0
0
0

2022-07-29 15:52:01
2510
636
0
0
0

2022-07-29 15:52:02
2510
636
0
0
0

2022-07-29 15:52:02
2509
646
0
0
0

2022-07-29 15:52:02
2510
632
0
0
0

2022-07-29 15:52:03
2509
646
0
0
0

2022-07-29 15:52:03
2510
636
0
0
0

2022-07-29 15:52:03
2510
647
0
0
0

2022-07-29 15:52:03
2510
636
0
0
0

2022-07-29 15:52:04
2509
647
0
0
0

2022-07-29 15:52:04
2509
635
0
0
0

2022-07-29 15:52:04
2509
637
0
0
0

2022-07-29 15:52:05
2510
637
0
0
0

2022-07-29 15:52:05
2510
636
0
0
0

2022-07-29 15:52:05
2509
647
0
0
0

2022-07-29 15:52:05
2509
636
0
0
0

2022-07-29 15:52:06
2510
647
0
0
0

2022-07-29 15:52:06
2510
635
0
0
0

2022-07-29 15:53:00
2510
635
0
0
0



SELECT 26


WITH DateGroups AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY received_on ORDER BY adc_v) AS rn
  FROM table_name
)
SELECT received_on, adc_v, adc_i, acc_axe_x, acc_axe_y, acc_axe_z 
FROM DateGroups
WHERE rn=1 
ORDER BY received_on


received_on
adc_v
adc_i
acc_axe_x
acc_axe_y
acc_axe_z


2022-07-29 15:52:00
2509
646
0
0
0

2022-07-29 15:52:01
2509
647
0
0
0

2022-07-29 15:52:02
2509
646
0
0
0

2022-07-29 15:52:03
2509
646
0
0
0

2022-07-29 15:52:04
2509
647
0
0
0

2022-07-29 15:52:05
2509
647
0
0
0

2022-07-29 15:52:06
2510
647
0
0
0

2022-07-29 15:53:00
2510
635
0
0
0



SELECT 8


WITH DateGroups AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY floor(extract(epoch from (received_on)) / 60) ORDER BY adc_v) AS rn
  FROM table_name
)
SELECT received_on, adc_v, adc_i, acc_axe_x, acc_axe_y, acc_axe_z 
FROM DateGroups
WHERE rn=1  
ORDER BY received_on


received_on
adc_v
adc_i
acc_axe_x
acc_axe_y
acc_axe_z


2022-07-29 15:52:01
2509
648
0
0
0

2022-07-29 15:53:00
2510
635
0
0
0



SELECT 2


WITH DateGroups AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY floor(extract(epoch from (received_on)) / (60*60)) ORDER BY adc_v) AS rn
  FROM table_name
)
SELECT received_on, adc_v, adc_i, acc_axe_x, acc_axe_y, acc_axe_z 
FROM DateGroups
WHERE rn=1  
ORDER BY received_on


received_on
adc_v
adc_i
acc_axe_x
acc_axe_y
acc_axe_z


2022-07-29 15:52:02
2509
646
0
0
0



SELECT 1

received_on	adc_v	adc_i
2022-07-29 15:52:00	2509	646
2022-07-29 15:52:00	2510	636
2022-07-29 15:52:00	2510	636
2022-07-29 15:52:00	2509	646
2022-07-29 15:52:00	2510	624
2022-07-29 15:52:01	2509	648
2022-07-29 15:52:01	2510	635
2022-07-29 15:52:01	2509	647
2022-07-29 15:52:01	2510	636
2022-07-29 15:52:02	2510	636
2022-07-29 15:52:02	2509	646
2022-07-29 15:52:02	2510	632
2022-07-29 15:52:03	2509	646
2022-07-29 15:52:03	2510	636
2022-07-29 15:52:03	2510	647
2022-07-29 15:52:03	2510	636
2022-07-29 15:52:04	2509	647
2022-07-29 15:52:04	2509	635
2022-07-29 15:52:04	2509	637
2022-07-29 15:52:05	2510	637
2022-07-29 15:52:05	2510	636
2022-07-29 15:52:05	2509	647
2022-07-29 15:52:05	2509	636
2022-07-29 15:52:06	2510	647
2022-07-29 15:52:06	2510	635
2022-07-29 15:53:00	2510	635