Query the Records Closest to the Specified Time Each Day — From SQL to SPL #43
Problem Description & Analysis:
Query the Records Closest to the Specified Time Each Day — From SQL to SPL #43
Problem Description & Analysis:
A table in Oracle database has a column of datetime type, corresponding to multiple pieces of data per day:
Task: Now we need to find two records every day, one closest to 8am that day and one closest to 8pm that day.
Solution:
SPL code:
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.
✅ esProc SPL FREE Download — Free Trial Available, Download Now!
메타데이터
- post_id
- 2eceaedcfd95
- slug
- query-the-records-closest-to-the-specified-time-each-day-from-sql-to-spl-43-2eceaedcfd95
- url
- https://medium.com/@esproc-spl/query-the-records-closest-to-the-specified-time-each-day-from-sql-to-spl-43-2eceaedcfd95
- canonical_url
- https://medium.com/@esproc-spl/query-the-records-closest-to-the-specified-time-each-day-from-sql-to-spl-43-2eceaedcfd95
- author_url
- https://medium.com/@esproc-spl
- status
- ok
- fetched_at
- 2026-08-03 13:38:41
