CODEWITHSUNDEEP
timescaledb
kristian mo
kristian mo

Reputation: 1486

Timescaledb: get first value before and after range

Given a time series range with values v0-v5 stored at irregular intervals in timescaledb.

v0     v1    v2   v3   v4 
t0 - t1 - t2 - t3 - t4 - t5

A range starting from >=t2 and to <=t4 is selected resulting in getting v2 and v3.

select time from table where time >= t2 and time <=t4 order by ASC

In addition I would like to get the first after and the first before the range so v1 and v4. The values are uneven so I do not know at what time t1 or t4 are. Could be a millisecond or years.

Not tested, but I assume could I could do this as explained in TimescaleDB: efficiently select last row

v1

select time from table where time < t2 order by desc LIMIT 1

v4

select time from table where time > t4 order by asc LIMIT 1

But this requires doing three queries, is there a way(s) to make it more efficient in timescaledb?

Upvotes: 3

Views: 418

Answers (1)

Attila Toth
Attila Toth

Reputation: 307

You could use subqueries to achieve this: (example query with range of '2021-04-01 04:04:00' - '2021-04-01 04:07:00')

SELECT time FROM table
WHERE 
    (time >= timestamp '2021-04-01 04:04:00' AND time <= timestamp '2021-04-01 04:07:00')
    OR time = (SELECT time FROM table WHERE time < timestamp '2021-04-01 04:04:00' ORDER BY time DESC LIMIT 1)
    OR time = (SELECT time FROM table WHERE time > timestamp '2021-04-01 04:07:00' ORDER BY time ASC LIMIT 1)
ORDER BY time

Upvotes: 4

Related Questions