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 temperature ( temp REAL, unix_time INTEGER, PRIMARY KEY (unix_time) ); CREATE TABLE relative_humidity ( rh REAL, unix_time INTEGER, PRIMARY KEY (unix_time) ); What I need to solve is this: 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. 2. I'd like to calculate averages for certain periods. Now, I could use AVG if everything was stored in fixed intervals, but that's not the case (which is also the reason why I want the interpolation above - the user can't know what valid timestamps are). For instance, let's say I want the average temperature for two consecutive days. The first day was cold and has one value for every hour. The second was hot and has one value for every minute. A plain AVG would not give me what I'd expect, since the hot day would get far too much weight. So when calculating my average, I need to weigth the values depending on the interval they'll represent. 3. Say that I want to know the dew point for a certain timestamp or the average dew point for a whole day. The dew point is calculated from the temperature and the relative humidity. The trouble is that the two tables don't contain the same timestamps, so I can't look up temp and rh in the tables using the timestamp and then do the calculations (unless problem 1 has been solved). In short, if my tables had values for every second, all these problems would go away. I could even use AVG and get what I wanted. Is it possible to create some kind of virtual table which to the user appears to have values for every second? 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? Is it possible to create a VIEW to do any of this? Could I use sqlite's virtual table functionality? Or is it best to leave the tables as they are and do what I want to do in C/C++, that is, to abandon the idea that I can get what I want using regular SQL statements. I could, of course, write a program that does all the interpolation for every second and store the interpolated values in the database, that would be very simple, but that would also make the database way too large and slow. Thanks. -- Steinar Midtskogen _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
