Re: [sqlite] Interpolation

[Simon Slavin] > On 10 Feb 2012, at 3:24pm, Steinar Midtskogen wrote: > >> I feared that. As it is, it takes 6 seconds to do a SELECT * FROM >> Combined LIMIT 1 ("Combined" is a view representing the merged table). >> If I add an ORDER BY, it takes 35 seconds. >> >> Any way to speed up the

Re: [sqlite] Interpolation

On 10 Feb 2012, at 3:24pm, Steinar Midtskogen wrote: > I feared that. As it is, it takes 6 seconds to do a SELECT * FROM > Combined LIMIT 1 ("Combined" is a view representing the merged table). > If I add an ORDER BY, it takes 35 seconds. > > Any way to speed up the ordering? Are you putting

Re: [sqlite] Interpolation

[Igor Tandetnik] > If you need a particular order, it's best to add an explicit ORDER BY. > Otherwise, you are at the mercy of an implementation. Your current version of > SQLite chooses an execution plan that happens, by accident, to produce rows > in the desired order. Tomorrow you upgrade

Re: [sqlite] Interpolation

Steinar Midtskogen wrote: > [Igor Tandetnik] >> Try something like this: >> >> select timestamp, value1, ..., value6 from >> (select timestamp from tab1 >> union >> select timestamp from tab2 >> union >> select timestamp from tab3) >> left join tab1 using (timespamp)

Re: [sqlite] Interpolation

[Igor Tandetnik] > Steinar Midtskogen wrote: >> >> Thanks, I didn't think in that simple terms. :) I think about listing >> all the values, so I got lost. I lost a word there: "I didn't think about listing"... >> >> But what if the tables share a timestamp, then I

Re: [sqlite] Interpolation

Steinar Midtskogen wrote: > [Igor Tandetnik] > >>> timestamp|value1|value2|value3|value4|value5|value6 >>> 1328873000|1|2| | | | >>> 1328873050| | |7| | | >>> 1328873075| | | |10|13|16 >>> 1328873100|3|4| | | | >>> 1328873150| | |8| | | >>> 1328873175| | |

Re: [sqlite] Interpolation

[Igor Tandetnik] >> timestamp|value1|value2|value3|value4|value5|value6 >> 1328873000|1|2| | | | >> 1328873050| | |7| | | >> 1328873075| | | |10|13|16 >> 1328873100|3|4| | | | >> 1328873150| | |8| | | >> 1328873175| | | |11|14|17 >> 1328873200|5|6| | | | >> 1328873250| | |9| | | >>

Re: [sqlite] Interpolation

Steinar Midtskogen wrote: > Let's say I have N tables, each with a > timestamp as primary key. For instance: > > tab1: > timestamp|value1|value2 > 1328873000|1|2 > 1328873100|3|4 > 1328873200|5|6 > > tab2: > timestamp|value3 > 1328873050|7 > 1328873150|8 > 1328873250|9 >

Re: [sqlite] Interpolation

I've rethought the interpolation strategy. It's not important to be able to look up any timestamp, just the timestamps that actually have values in at least one table. Let's say I have N tables, each with a timestamp as primary key. For instance: tab1: timestamp|value1|value2 1328873000|1|2

Re: [sqlite] Interpolation

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/02/12 01:47, Steinar Midtskogen wrote: > OK. But then I wont have an SQL interface for accessing interpolated > data. It's acceptable. An SQL interface is mostly a "nice to have", > so I could do some quick queries in SQL instead of having

Re: [sqlite] Interpolation

[Kit] > SELECT a.temp+(b.temp-a.temp)/(b.time-a.time)*(strftime('%s','2012-02-08 > 11:37:00')-a.time) FROM > (select time, temp FROM tp > WHERE strftime('%s','2012-02-08 11:37:00')*1>=time > ORDER BY time DESC LIMIT 1) AS a, > (select time, temp FROM tp > WHERE

Re: [sqlite] Interpolation

[Kevin Martin] > For the quick and dirty solution, I think you can use something like this to > create your view. You would need to index time, and even with the index, I'm > not too sure about speed. > > select x1.time, x1.value, x2.time from x as x1 left join x as x2 on > x2.time=(select

Re: [sqlite] Interpolation

2012/2/8 Steinar Midtskogen : > 1. I'd like to be able to look up any timestamp between the oldest and > the newest in the database, and if there is no value stored for that > timestamp, the value given should be an interpolation of the two > closest.  So, if the table has:

Re: [sqlite] Interpolation

For the quick and dirty solution, I think you can use something like this to create your view. You would need to index time, and even with the index, I'm not too sure about speed. select x1.time, x1.value, x2.time from x as x1 left join x as x2 on x2.time=(select max(time) from x where

Re: [sqlite] Interpolation

Related to this thread, I wonder if it's possible to create a view which can give me a value from the row immediately above. E.g. given the table: unix_time val --+--- 1325376000|val1 1325376300|val2 1325376600|val3 1325376900|val4 (the first column is a unix timestamp and unique) can

Re: [sqlite] Interpolation

[Roger Binns] > I'd recommend you write code in your application first that knows how to > calculate the values you want. That way you can ensure the calculations > are correct, you have something for test harnesses that produces "good" > values and you have something to port to your final

Re: [sqlite] Interpolation

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/02/12 23:14, Steinar Midtskogen wrote: > I'm not asking the list to solve these problems for me, but it would > greatly help if anyone could point me to the right direction. Which > approach would work? I'd recommend you write code in your

[sqlite] Interpolation

Hello I have a large (> 1GB) collection of text files containing sensor values associated with timestamps, and I want to switch to an sqlite database. For simplicity, let's assume that I have two tables, one that stores temperatures and another that stores relative_humidity: CREATE TABLE