[sqlite] Selecting every nth row efficiently

I have a big database with timestamps and sensor readings, which I access with SELECT to have gnuplot draw graphs. However, sometimes I have readings every minute and want to plot several years of data, and feeding everything to gnuplot is overkill. In these cases it would be sufficient to

Re: [sqlite] Parser bug on sub joins when on right side of the join operator

Hi, I expected someone would reply to this one Mathieu -Message d'origine- De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] De la part de Mathieu TAUZIN Envoyé : lundi 26 mars 2012 15:53 À : sqlite-users@sqlite.org Objet : [sqlite] Parser bug on sub

Re: [sqlite] Selecting every nth row efficiently

I have a big database with timestamps and sensor readings, which I access with SELECT to have gnuplot draw graphs. However, sometimes I have readings every minute and want to plot several years of data, and feeding everything to gnuplot is overkill. In these cases it would be sufficient to

Re: [sqlite] Selecting every nth row efficiently

Hit Send inadvertandly, sorry. My rowid isn't increasing with something predictable, so I can't do something like WHERE rowid % n = 0. I can use WHERE random() % n = 0 giving me sort of what I want (better than row % n, but I still need something better). If your sampling is essentially

Re: [sqlite] Selecting every nth row efficiently

[Jean-Christophe Deschamps] >>If your sampling is essentially regular, why not make it >> >>select ... where timestamp % N between min_interval and max_interval >> >> N being the typical time delta of your n rows above and interval >> bounds reducing the possiblity of gross under- and

Re: [sqlite] Selecting every nth row efficiently

This is a good suggestion. A drawback is that the interval can't be too small otherwise there is a risk that a sample would be missed. So I will get more samples than I need. In you example, if there is a sample every second more or less, I would usually get 3-4 samples every hour instead of

Re: [sqlite] Selecting every nth row efficiently

[Jean-Christophe Deschamps] > You're going to have at most one random sample in every slice of 320 > s. The GROUP BY clause will select only one for you and the query can > be as simple as: > > select sample from from mytable group by timestamp / 320 order by > timestamp; Ah. I didn't think of

Re: [sqlite] Selecting every nth row efficiently

Let's say that I want to plot the entire column and the plot is 1000 pixels wide. Then I only need 1000 samples, so I could do this: SELECT timestamp, sample FROM mytable GROUP BY timestamp * 1000 / ((SELECT max(timestamp) FROM mytable) - (SELECT min(timestamp) FROM mytable)); (timestamp is

Re: [sqlite] Hashing 2 SQLite db files with the same data

On Wed, Apr 04, 2012 at 11:15:10AM +1000, Webdude scratched on the wall: > But the same SQLite version, using the same schema, setup with the > same PRAGMA's, creating a db with the same data and in the same > order, and despite hardware / HDD / OS, should still produce the > same file byte-for

Re: [sqlite] Hashing 2 SQLite db files with the same data

On Wed, Apr 04, 2012 at 11:27:41AM +1000, Webdude scratched on the wall: > But if data was added exactly in the same way/order shouldn't the > counters all count to the same end result if the process was > repeated at a later time on a another machine? In theory, yes, but that's a very thin

Re: [sqlite] Hashing 2 SQLite db files with the same data

Howeverthe DB file is portable across big/little endian and 32/64 bit. So do your hash on the DB file and distribute that. Any reason you can't do that? http://www.sqlite.org/onefile.html I guess SQLite uses the endianess of the database file over the architecture? Michael D.

Re: [sqlite] Hashing 2 SQLite db files with the same data

On 4 Apr 2012, at 1:30pm, "Black, Michael (IS)" wrote: > Howeverthe DB file is portable across big/little endian and 32/64 bit. > > So do your hash on the DB file and distribute that. Any reason you can't do > that? My understanding is that he's having two

[sqlite] sqlite3VdbeSetSql should report out of memory condition

Currently if the call p->zSql = sqlite3DbStrNDup(p->db, z, n); fails, zSql will stay null without the caller being notified that it failed. sqlite3VdbeSetSql should probably return a bool. With that, sqlite3Prepare could check it and return SQLITE_NOMEM. Cheers, Rafael

Re: [sqlite] sqlite3VdbeSetSql should report out of memory condition

2012/4/4 Rafael Ávila de Espíndola > Currently if the call > > p->zSql = sqlite3DbStrNDup(p->db, z, n); > > fails, zSql will stay null without the caller being notified that it > failed. sqlite3VdbeSetSql should probably return a bool. With that, > sqlite3Prepare could

Re: [sqlite] Hashing 2 SQLite db files with the same data

On Wed, Apr 04, 2012 at 12:30:48PM +, Black, Michael (IS) scratched on the wall: > Howeverthe DB file is portable across big/little endian and 32/64 bit. Yes. As I said, SQLite will automatically convert values when the file and platform endian don't match. > I guess SQLite uses

Re: [sqlite] Selecting every nth row efficiently

On Wed, Apr 04, 2012 at 12:05:44PM +0200, Steinar Midtskogen scratched on the wall: > [Jean-Christophe Deschamps] > > > You're going to have at most one random sample in every slice of 320 > > s. The GROUP BY clause will select only one for you and the query can > > be as simple as: > > > >

Re: [sqlite] sqlite3VdbeSetSql should report out of memory condition

On 12-04-04 11:20 AM, Richard Hipp wrote: 2012/4/4 Rafael Ávila de Espíndola Currently if the call p->zSql = sqlite3DbStrNDup(p->db, z, n); fails, zSql will stay null without the caller being notified that it failed. sqlite3VdbeSetSql should probably return a bool.

[sqlite] About INSERT into "External Content FTS4 Tables"

Why INSERT command can't copy all needed fields from external content table same as "rebuild" command? The definition of all values does not has any reasons and can produce inconsistent FTS table. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/

[sqlite] SQLite & JDBC & generated key

* In JDBC API, there is a method to retreive the generated key during an insert: http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys() * With SQLite API, there is: sqlite3_last_insert_rowid. Let suppose that: - the primary key is correctly declared to make it an

Re: [sqlite] SQLite & JDBC & generated key

On 4 Apr 2012, at 8:08pm, gwenn wrote: > * In JDBC API, there is a method to retreive the generated key during an > insert: > http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys() > * With SQLite API, there is: sqlite3_last_insert_rowid. >

Re: [sqlite] Selecting every nth row efficiently

On Wed, 4 Apr 2012 10:59:20 -0500, "Jay A. Kreibich" wrote: > Not to mention avg(). > > You might want to have a look at how RRDtool deals with condensing > data. It is common pratice to plot average, min, and max to preserve > outliers, while still showing trends. > >

[sqlite] ETL anomalies

First - Dr. Hipp - thanks for the .import fix from 1/16 baked into 3.7.11 - it now recognizes commas embedded within character strings in a csv + strips the double-quotes. This makes the loading much smoother. == Now for 3 strange events I've been running into. Sorry - the data

Re: [sqlite] SQLite & JDBC & generated key

On Wed, 4 Apr 2012 21:08:24 +0200, gwenn wrote: > * In JDBC API, there is a method to retreive the generated key during an > insert: >http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys() > * With SQLite API, there is:

Re: [sqlite] SQLite & JDBC & generated key

On Wed, 4 Apr 2012 21:08:24 +0200, gwenn wrote: > 2) Do you know if there are other bindings that implement/support > this kind of feature ? I almost forgot to mention: SELECT last_insert_rowid(); http://www.sqlite.org/lang_corefunc.html -- Regards, Kees Nuyt

[sqlite] INSERT INTO with SELECT

I'm seeing something that doesn't look right when using the SELECT form of the INSERT statement. Here's my test: CREATE TABLE t2 (Col1 text,Col2 text); insert into t2 (Col1) values('xxx'); select * from t2 xxx| CREATE TABLE t3 (Col1 text,Col2 text default 'abc'); insert into t3 SELECT * FROM

Re: [sqlite] INSERT INTO with SELECT

On 5 Apr 2012, at 1:36am, Pete wrote: > Here's my test: > > CREATE TABLE t2 (Col1 text,Col2 text); > insert into t2 (Col1) values('xxx'); > select * from t2 > xxx| > > CREATE TABLE t3 (Col1 text,Col2 text default 'abc'); > insert into t3 SELECT * FROM t2; > select *

[sqlite] Variation on INSERT with SELECT issue

The test this time was: sqlite> create table t1 (Col1,Col2); sqlite> insert into t1 (Col1) values ('xxx'); sqlite> select * from t1; xxx| sqlite> create table t2 (Col1, col2 not null default 'abc'); sqlite> insert into t2 SELECT * from t1; SQL error: t2.col2 may not be NULL -- Pete

Re: [sqlite] Variation on INSERT with SELECT issue

On 4/4/2012 8:48 PM, Pete wrote: The test this time was: sqlite> create table t1 (Col1,Col2); sqlite> insert into t1 (Col1) values ('xxx'); sqlite> select * from t1; xxx| sqlite> create table t2 (Col1, col2 not null default 'abc'); sqlite> insert into t2 SELECT * from t1; SQL error:

[sqlite] about The open-source Fossil version control system

Date: Tue, 03 Apr 2012 20:56:15 +0200 Kees Nuyt wrote: >The open-source Fossil version control system >used by SQLite contains an implementation of system() that correctly deals >with UTF8 even on windows systems. Perhaps the OP can copy some of