RaqForum 41 No.
83 View  • 
     ...

SQL: Query the records closest to the specified time each day

 

A table in Oracle database has a column of datetime type, corresponding to multiple pieces of data per day:

t

d

1.1.2024 08:08:08

1

1.1.2024 10:10:10

2

1.1.2024 15:15:15

3

1.1.2024 20:20:20

4

2.1.2024 09:09:09

5

2.1.2024 12:12:12

6

2.1.2024 16:16:16

7

12.12.2024 16:16:16

8

Now we need to find two records every day, one closest to 8am that day and one closest to 8pm that day.

t

d

1.1.2024 08:08:08

1

1.1.2024 20:20:20

4

2.1.2024 09:09:09

5

2.1.2024 16:16:16

7

12.12.2024 16:16:16

8

12.12.2024 16:16:16

8

SPL code:

　A

1

=orcl.query("select * from tb”)

2

=A1.group(day(t))

3

=A2.conj([~.minp(abs(interval@s(time("08:00:00"),time(t)))),~.minp(abs(interval@s(time("20:00:00"),time(t))))])

A1: Query the database through JDBC.

A2: Group by date, but do not aggregate for subsequent processing of each group of data.

A3: For each group of data, calculate the number of seconds between each record in the group and 8 am on the same day, take the absolute value, and find the record with the smallest absolute value; Calculate the record with the smallest absolute value of the number of seconds between 8 pm using the same method; Finally, merge the processed results of each group. The minp function is used to calculate the minimum record that meets the criteria.

Problem source：https://stackoverflow.com/questions/78389189/records-closest-to-the-time


 
 •  83 View  •