DEV Community

Judy
Judy

Posted on

2 1 1 1 1

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

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

Image description

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

Image description

SPL code:

Image description

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

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.

Open source SPL source address

Free Download

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more