Re: [sqlite] calculation with the result of two select results

2017-10-30 Thread Keith Medcalf
You should probably declare your table thusly: create table werte ( minutestamp integer default (strftime('%s', 'now') / 60), ... ); create index werte_minutestamp on werte (minutestamp); then your "minutestamp" is in minutes since the Unix epoch and you can create an index on it.

Re: [sqlite] Performance issue for version 3.9.2 compared with 3.16.2

2017-10-30 Thread Richard Hipp
On 10/17/17, advancenOO wrote: > In order to profile sqlite 3.9.2 and sqlite 3.16.2, I run speedtest1.c on my > mobile phone. > It is weird to find that 3.9.2 is better than 3.16.2, especially in the > following case: > “270, 1 DELETEs, numeric BETWEEN,

[sqlite] make install tries to update /usr/share/tcl8.6 regardless of --prefix= value

2017-10-30 Thread John McKown
Just as background: I updated my sqlite3 directory using the "fossil pull && fossil checkout branch-3.21 --force" commands. I then did a "autoconf", followed by an "./configure --prefix=$PWD/bld ...". I then did a "make" followed by a "make install". The first "make" ran just fine. But the "make

Re: [sqlite] calculation with the result of two select results

2017-10-30 Thread Wout Mertens
Yes, so the two strftimes for now are called once beforehand, and then indeed each row is visited and strftime calculated. If you want you can create an expression index for the strftime and then lookups will be super fast at the cost of some index space. On 10/30/17, nitpi...@arcor.de

Re: [sqlite] calculation with the result of two select results

2017-10-30 Thread nitpilot
Hi Richard, I'm not sure, what You mean. My intention was to drop the seconds while finding the correct rows. The data for the records is collected by a perl script and this stores the records sometimes at hh:mm:09 sometimes at hh:mm:10. The timestamp is assigned automaticly while creating

[sqlite] sqlite3PcacheCleanAll() is CPU Bound on Windows 2K8R2

2017-10-30 Thread Jacob Fehr
Good Morning all, I am hoping to get some help with an issue that we see periodically. We have a machine that does low volume, low stress but long term testing of our application. We have been able to get SQLite to become CPU bound after about 3 days of longevity testing on Windows Server 2008

Re: [sqlite] calculation with the result of two select results

2017-10-30 Thread Richard Hipp
On 10/30/17, Wout Mertens wrote: >> WHERE STRFTIME('%Y-%m-%d %H:%M', timestamp) = STRFTIME('%Y-%m-%d %H:%M', > 'now', 'localtime', '-1 minute'); > > Won't this run strftime on all rows? Unless you have a calculated index on > that strftime function, I think you should

Re: [sqlite] calculation with the result of two select results

2017-10-30 Thread Wout Mertens
> WHERE STRFTIME('%Y-%m-%d %H:%M', timestamp) = STRFTIME('%Y-%m-%d %H:%M', 'now', 'localtime', '-1 minute'); Won't this run strftime on all rows? Unless you have a calculated index on that strftime function, I think you should convert the 'now' to a timestamp… Unless of course your table is 5

Re: [sqlite] calculation with the result of two select results

2017-10-30 Thread nitpilot
On Monday, 30 October 2017 07:27:38 CET Richard Hipp wrote: > On 10/30/17, nitpi...@arcor.de wrote: > > I was trying with parenthesizing but without luck. > > (SELECT ... ) - (SELECT ... -1 day ...); > > You want: > > SELECT (SELECT ...)-(SELECT ... -1 day ...); OMG, so

Re: [sqlite] calculation with the result of two select results

2017-10-30 Thread Richard Hipp
On 10/30/17, nitpi...@arcor.de wrote: > > I was trying with parenthesizing but without luck. > (SELECT ... ) - (SELECT ... -1 day ...); You want: SELECT (SELECT ...)-(SELECT ... -1 day ...); -- D. Richard Hipp d...@sqlite.org ___

Re: [sqlite] optimizing min/max with calculated index?

2017-10-30 Thread Wout Mertens
Works great for me! On Thu, Oct 26, 2017 at 10:13 PM Richard Hipp wrote: > Y'all please try the lastest trunk version of SQLite for me and let me > know if it works better for you. Thanks. > > -- > D. Richard Hipp > d...@sqlite.org >

[sqlite] calculation with the result of two select results

2017-10-30 Thread nitpilot
Hi gurus, I have a database from which I get two (integer) values, one from today and the second from same time yesterday: SELECT kwh_th FROM werte WHERE STRFTIME('%Y-%m-%d %H:%M', timestamp) = STRFTIME('%Y-%m-%d %H:%M', 'now', 'localtime', '-1 minute'); SELECT kwh_th FROM werte WHERE

[sqlite] Fossil comunication and or test problems

2017-10-30 Thread Domingo Alvarez Duarte
Hello ! I'm writing this here on sqlite lists because I already sent a direct mail to Richard Hipp on 21/10/2017 and to fossil-scm lists on 23/10/2017 and no reply or action so far. Maybe there is a comunication problem preventing those emails reach the developers. The test problem is