Oracle 21c

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

CREATE TABLE t1 (time DATE, some_data NUMBER);
INSERT INTO t1 VALUES(TO_DATE('1.1.2024 08:08:08', 'dd.mm.yyyy hh24:mi:ss'), 1);
1 rows affected

INSERT INTO t1 VALUES(TO_DATE('1.1.2024 10:10:10', 'dd.mm.yyyy hh24:mi:ss'), 2);
1 rows affected

INSERT INTO t1 VALUES(TO_DATE('1.1.2024 15:15:15', 'dd.mm.yyyy hh24:mi:ss'), 3);
1 rows affected

INSERT INTO t1 VALUES(TO_DATE('1.1.2024 20:20:20', 'dd.mm.yyyy hh24:mi:ss'), 4);
1 rows affected

INSERT INTO t1 VALUES(TO_DATE('2.1.2024 09:09:09', 'dd.mm.yyyy hh24:mi:ss'), 5);
1 rows affected

INSERT INTO t1 VALUES(TO_DATE('2.1.2024 12:12:12', 'dd.mm.yyyy hh24:mi:ss'), 6);
1 rows affected

INSERT INTO t1 VALUES(TO_DATE('2.1.2024 16:16:16', 'dd.mm.yyyy hh24:mi:ss'), 7);
1 rows affected

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'
select t1.time, t1.some_data,
  trunc(t1.time) + interval '8' hour - time as r1,
  abs(trunc(t1.time) + interval '8' hour - time) as r2,
  trunc(t1.time) + interval '20' hour - time as r3,
  abs(trunc(t1.time) + interval '20' hour - time) as r4
from t1


TIME
SOME_DATA
R1
R2
R3
R4


2024-01-01 08:08:08
1
-.005648148148148148148148148148148148148148
.005648148148148148148148148148148148148148
.4943518518518518518518518518518518518519
.4943518518518518518518518518518518518519

2024-01-01 10:10:10
2
-.0903935185185185185185185185185185185185
.0903935185185185185185185185185185185185
.4096064814814814814814814814814814814815
.4096064814814814814814814814814814814815

2024-01-01 15:15:15
3
-.3022569444444444444444444444444444444444
.3022569444444444444444444444444444444444
.1977430555555555555555555555555555555556
.1977430555555555555555555555555555555556

2024-01-01 20:20:20
4
-.5141203703703703703703703703703703703704
.5141203703703703703703703703703703703704
-.0141203703703703703703703703703703703704
.0141203703703703703703703703703703703704

2024-01-02 09:09:09
5
-.0480208333333333333333333333333333333333
.0480208333333333333333333333333333333333
.4519791666666666666666666666666666666667
.4519791666666666666666666666666666666667

2024-01-02 12:12:12
6
-.1751388888888888888888888888888888888889
.1751388888888888888888888888888888888889
.3248611111111111111111111111111111111111
.3248611111111111111111111111111111111111

2024-01-02 16:16:16
7
-.3446296296296296296296296296296296296296
.3446296296296296296296296296296296296296
.1553703703703703703703703703703703703704
.1553703703703703703703703703703703703704


select t1.time, t1.some_data,
  rank() over (partition by trunc(time)
    order by abs(trunc(t1.time) + interval '8' hour - time)) as rnk8,
  rank() over (partition by trunc(time)
    order by abs(trunc(t1.time) + interval '20' hour - time)) as rnk20
from t1


TIME
SOME_DATA
RNK8
RNK20


2024-01-01 08:08:08
1
1
4

2024-01-01 10:10:10
2
2
3

2024-01-01 15:15:15
3
3
2

2024-01-01 20:20:20
4
4
1

2024-01-02 09:09:09
5
1
3

2024-01-02 12:12:12
6
2
2

2024-01-02 16:16:16
7
3
1


select time, some_data
from (
  select t1.time, t1.some_data,
    rank() over (partition by trunc(time)
      order by abs(trunc(t1.time) + interval '8' hour - time)) as rnk8,
    rank() over (partition by trunc(time)
      order by abs(trunc(t1.time) + interval '20' hour - time)) as rnk20
  from t1
)
where rnk8 = 1
or rnk20 = 1
order by time


TIME
SOME_DATA


2024-01-01 08:08:08
1

2024-01-01 20:20:20
4

2024-01-02 09:09:09
5

2024-01-02 16:16:16
7

TIME	SOME_DATA	R1	R2	R3	R4
2024-01-01 08:08:08	1	-.005648148148148148148148148148148148148148	.005648148148148148148148148148148148148148	.4943518518518518518518518518518518518519	.4943518518518518518518518518518518518519
2024-01-01 10:10:10	2	-.0903935185185185185185185185185185185185	.0903935185185185185185185185185185185185	.4096064814814814814814814814814814814815	.4096064814814814814814814814814814814815
2024-01-01 15:15:15	3	-.3022569444444444444444444444444444444444	.3022569444444444444444444444444444444444	.1977430555555555555555555555555555555556	.1977430555555555555555555555555555555556
2024-01-01 20:20:20	4	-.5141203703703703703703703703703703703704	.5141203703703703703703703703703703703704	-.0141203703703703703703703703703703703704	.0141203703703703703703703703703703703704
2024-01-02 09:09:09	5	-.0480208333333333333333333333333333333333	.0480208333333333333333333333333333333333	.4519791666666666666666666666666666666667	.4519791666666666666666666666666666666667
2024-01-02 12:12:12	6	-.1751388888888888888888888888888888888889	.1751388888888888888888888888888888888889	.3248611111111111111111111111111111111111	.3248611111111111111111111111111111111111
2024-01-02 16:16:16	7	-.3446296296296296296296296296296296296296	.3446296296296296296296296296296296296296	.1553703703703703703703703703703703703704	.1553703703703703703703703703703703703704