There is a table of the form, size ~ 10 tons of records: timestamp │ value ─────────────────────────┼────────── 2017-01-17T10:47:13.757Z │ 12.34 2017-01-17T10:48:23.261Z │ 15.65 2017-01-17T10:51:13.254Z │ 17.56 There is a separate list of times Z, ~ 50 points. You need to get a table of the form z[i],t0,v0,t1,v1 where z [i] is a point from the list, data from the table, with a maximum time not exceeding z [i], - with a minimum time but longer z [i] . Those find the values ​​that precede and immediately follow the desired point. If the time before or after does not exist, then let it be t0, v0 or t1, v1 be NULL.

Question 1. Where is the best place to keep data for table Z. For example, you can start a temporary table for the duration of the calculation, and then destroy it.

Question 2. How to write a SQL query that make the necessary sample.

PS Base sqlite, everything works under node.js The task is this: You need to build a temperature graph. The data from the sensors are added to the table at any time. The difference between the records can be from 10 seconds to 30 minutes. It would be desirable to receive average data.

    1 answer 1

    To create a table or not - decide for yourself, if you intend to use the same set of Z several times, it is better to create it. And you can directly in the request to specify a list of values.

     with Z(t) as ( values('2017-01-17T10:49:00.000Z'), ('2017-01-16T10:49:00.000Z') ) select t, substr(p,1,24) t0, substr(p,25) v0, substr(n,1,24) t1, substr(n,25) v1 from ( select t, (select cast(tab.timestamp as text)||"value" from tab where tab.timestamp<Zt order by tab.timestamp desc limit 1) p, (select cast(tab.timestamp as text)||"value" from tab where tab.timestamp>=Zt order by tab.timestamp limit 1) n from Z ) A

    Of the minuses - all results are returned by text fields, you need to lead to the required type. With numbers, this is not a problem. cast(xx as real) will again make a numeric value, but for some reason it does not want to give back to the timestamp.

    Test on sqlfiddle.com

    • Can I abandon table Z and make a sql query if table Z represents times from one date to another in X minutes increments? Does it make sense to drop the datetime type in favor of an integer? - Konstantin
    • @ Konstantin Yes, with an integer will be clearly easier. the more you can then group the data by time integrally divided by N to take the minimum-maximums - here you have the intervals in N seconds - Mike
    • Can you show an example? Let for example a table look like this: `timestamp | value 1 | 1.23 7 | 4.56 13 | 7.86 ... `we need to find the nearest values ​​for the scale from 0 to 20 in increments of 5 - Konstantin