JudyA table in Oracle database has a column of datetime type, corresponding to multiple pieces of data per day:
Now we need to find two records every day, one closest to 8am that day and one closest to 8pm that day.
SPL code:
=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.
