Ryan Johnson <ryan.john...@cs.utoronto.ca> writes:
> On 19/10/2012 3:09 PM, Steinar Midtskogen wrote:
>> I have tables with a timestamp (unix time) and columns containing
>> sensor readings which are inserted continuously. I frequently need to
>> access the most recent values (or NULL if there is no value within the
>> latest, say, hour). I would like to do something like:
> Is each column associated with a different sensor?
Yes.
> If so, do sensors routinely generate data at the same time and combine their
> entries?
They usually generate data at the same time, but not always.
> Do sensors emit data frequently?
Usually every 5 minutes, but since the timestamp is unix time, 1
second frequency is supported.
> If any of the above is false, I'd definitely store each column in its
> own table, because the timestamp (the only possible thing to share) is
> replicated anyway.
I have many tables, but each has up to 30 or so values. The table
division corresponds to the sensor source. One source will typically
give a bunch of sensor values every 5 minutes (some values might be
NULL, though). This makes INSERTs trivial. I suppose I could put
everything in a single table by using UPDATEs, but since the different
sources have different timestamps and even intervals, I've been
thinking that it could make the database consist of mostly NULLs and
explode in size (say, if one table has readings every second, a single
table would have to have 3599 NULLs for each value in a column that
only have one value per hour).
The other extreme, one table for each sensor, has its problems as
well. Quite often, I'd like to combine several values in one
expression (e.g. subtract one sensor value from another), and that may
be a bit tricky if they are located in different tables, especially if
they don't have the same timestamps.
Anyway, for this purpose I made a module which allows me to combine
several tables into a read-only virtual table similar to a natural
full outer join (I think). Also, the virtual table will fill out all
missing values using interpolation. I've also added functionality for
calculating time weighted averages.
It's pretty neat. I wrote a perl wrapper as well which will look at
the columns that I want to access and it will create the necessary
virtual tables so I don't have to worry about which tables.
So if I want the outside temperature at 14 October 12:12:!2 even if
there is no table entry for that time, I can do:
$ select.pl 'temp_out WHERE unix_time = strftime("%s", "2012-10-14 12:12:12")'
0.93384
And in this case the result is an interpolation using the nearest
entries at 12:10:00 and 12:15:00. If I want the 24 hour mean around
that time I do:
$ select.pl 'temp_out__avg_24h WHERE unix_time = strftime("%s", "2012-10-14
12:12:12")'
1.70068
where the __avg_24h bit is parsed so that an approperiate virtual
table gets created that will give me the 24h average. And I can
combine columns from different tables, as in this case:
$ select.pl 'temp_in, temp_out, temp_in - temp_out WHERE unix_time =
strftime("%s", "2012-10-14 12:12:12")'
21.52852 0.93384 20.59468
where temp_in is in one table and temp_out is in another and neither
table has a row for 12:12:12. And I can even use different averages
in the same expression, which will cause two virtual tables to become
created behind the scenes:
$ select.pl 'temp_out__avg_1y, temp_out__avg_24h, temp_out__avg_1y -
temp_out__avg_24h WHERE unix_time = strftime("%s", "2010-01-01 00:00:00")'
4.40974 -7.61093 12.02068
I'm getting off topic, but the essence is that I want to do flexible
queries with as little typing as possible. If it can be done in plain
SQL, I'll do that. If not, if it can be done by using user functions
or modules, I'll do that. If I can't do that either, I'll add an
outer wrapper translating quasi SQL into SQLite commands. For the
problem in this thread it seems that I can get away with adding new
aggregate functions.
--
Steinar Midtskogen
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
