2012/2/8 Steinar Midtskogen <stei...@latinitas.org>:
> 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:
>
> 1325376000 (Jan 1 2012 00:00:00 UTC) | 5.0
> 1325376300 (Jan 1 2012 00:05:00 UTC) | 10.0
>
> and I do "SELECT temp FROM temperature WHERE unix_time = 1325376120"
> (00:02:00) I should get 7.0.
create table tp (time default (datetime('%s','now')), temp real);
insert into tp values (1328700574,2.5);
insert into tp values (1328701004,4.8);
insert into tp values (1328701060,5.1);
insert into tp values (1328701093,5.2);
select datetime(time,'unixepoch'),temp from tp;
-- 2012-02-08 11:29:34|2.5
-- 2012-02-08 11:36:44|4.8
-- 2012-02-08 11:37:40|5.1
-- 2012-02-08 11:38:13|5.2
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 strftime('%s','2012-02-08 11:37:00')*1<time
ORDER BY time LIMIT 1) AS b;
-- 4.88571428571429
--
Kit
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
