Re: [sqlite] sqlite3_analyzer for Debian

[nitpilot]

Am Thu, 7 Dec 2017 19:29:46 +
schrieb Simon Slavin :

> It’s not part of the standard Debian installation, but you can
> download it as part of the precompiled binaries for your platform:
> 
> 
> 
> Note that sqlite3_analyzer is a compiled Tcl program.  It should work
> fine under any Linux variant but if you have any trouble with it
> please post here.
> 
> Simon.

Hi Simon,

be careful with "under any Linux" as the name of the zip says the
binarys are for X86 only: sqlite-tools-linux-x86-321.zip

On Raspberry it will not work.

Matth
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] finding the number of records until a value is different

[nitpilot]

Hi all,

I have a DB i.e. like this:

table values
bc  temp
35  123
35  124
35  123
20  123
12  123
12  123
16  123
35  123
35  123
35  123
35  123
35  123

The value in temp (or all the other columns) is not of interest.

Now I a looking for the basic concept how to count the number of rows where 
bc=bc_of_last_row after the last different bc.

SELECT COUNT(bc) FROM values WHERE bc=35;

gives me the number of all rows with bc=35, so here 8.
But I am looking for 5, the number of rows after the last change of bc. 

Regards Matth
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 using the default...:
sqlite> .schema
CREATE TABLE "werte"(timestamp datetime default (datetime (current_timestamp, 
'localtime')), ...

A cronjob is starting a shellscript which is starting 
sqlite3  .open /dev/shm/log.db
sqlite> EXPLAIN SELECT (
   ...> 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 STRFTIME('%Y-%m-%d %H:%M', timestamp) = 
STRFTIME('%Y-%m-%d %H:%M', 'now', 'localtime', '-1 days', '-1 minute')
   ...> );  


0|Init|0|28|0||00|  


1|Once|0|13|0||00|  


2|Null|0|3|0||00|   


3|Integer|1|4|0||00|


4|OpenRead|0|2|0|6|00|  


5|Rewind|0|12|0||00|


6|Column|0|0|7||00| 


7|Function|1|6|5|strftime(-1)|02|   


8|Ne|8|11|5||51|


9|Column|0|5|3||00| 


10|IfZero|4|12|-1||00|  


11|Next|0|6|0||01|  


12|Close|0|0|0||00| 


13|Once|1|25|0||00| 


14|Null|0|9|0||00|  


15|Integer|1|10|0||00|  


16|OpenRead|1|2|0|6|00| 


17|Rewind|1|24|0||00|
18|Column|1|0|12||00|
19|Function|1|11|5|strftime(-1)|02|
20|Ne|13|23|5||51|
21|Column|1|5|9||00|
22|IfZero|10|24|-1||00|
23|Next|1|18|0||01|
24|Close|1|0|0||00|
25|Subtract|9|3|1||00|
26|ResultRow|1|1|0||00|
27|Halt|0|0|0||00|
28|Transaction|0|0|6|0|01|
29|TableLock|0|2|0|werte|00|
30|String8|0|6|0|%Y-%m-%d %H:%M|00|
31|String8|0|14|0|%Y-%m-%d %H:%M|00|
32|String8|0|15|0|now|00|
33|String8|0|16|0|localtime|00|
34|String8|0|17|0|-1 minute|00|
35|Function|15|14|8|strftime(-1)|04|
36|String8|0|11|0|%Y-%m-%d %H:%M|00|
37|String8|0|18|0|%Y-%m-%d %H:%M|00|
38|String8|0|19|0|now|00|
39|String8|0|20|0|localtime|00|
40|String8|0|21|0|-1 days|00|
41|String8|0|22|0|-1 minute|00|
42|Function|31|18|13|strftime(-1)|05|
43|Goto|0|1|0||00|
sqlite>

On Monday, 30 October 2017 12:10:30 CET Richard Hipp wrote:
> On 10/30/17, Wout Mertens 

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

[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 easy! 
Thank You very much.

Matth

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] calculation with the result of two select results

[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 STRFTIME('%Y-%m-%d %H:%M', timestamp) = 
STRFTIME('%Y-%m-%d %H:%M', 'now', 'localtime', '-1 days', '-1 minute');

The two numbers are correct.
How can I get the difference of those two numbers instead of the two numbers as
result of the SQL statement?

I was trying with parenthesizing but without luck.
(SELECT ... ) - (SELECT ... -1 day ...);

Also I was thinking about using variables like:
today = SELECT ...;
yesterday = SELECT ...;
print yesterday - today;
But I could not find informations which got me solving this.

Is there a way to do this in a SQL statement or do I have to use a programming 
language on top of the sqlite level?

Regards Mathh
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users