calculation with the result of two select results

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: calculation with the result of two select results

Richard Hipp-3
On 10/30/17, [hidden email] <[hidden email]> wrote:
>
> I was trying with parenthesizing but without luck.
> (SELECT ... ) - (SELECT ... -1 day ...);

You want:

SELECT (SELECT ...)-(SELECT ... -1 day ...);

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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, [hidden email] <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: calculation with the result of two select results

wmertens
> 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 rows long :)

On Mon, Oct 30, 2017 at 3:21 PM <[hidden email]> wrote:

> On Monday, 30 October 2017 07:27:38 CET Richard Hipp wrote:
> > On 10/30/17, [hidden email] <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: calculation with the result of two select results

Richard Hipp-3
On 10/30/17, Wout Mertens <[hidden email]> 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 convert the 'now' to a timestamp…
>

The first STRFTIME() does run for every row, because timestamp is
different for every row.  But the second STRFTIME() should be factored
out and run only once.  Please verify that this is happening by
looking at the output of EXPLAIN, and report the problem to me if you
find out otherwise.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 </home/.../daily_status.sql
This happens always shortly after the beginning of a minute, so the risk
that the both 'now' deliver different results is very low.

Regards Matth

Output of EXPLAIN:
sqlite> .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 <[hidden email]> 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 convert the 'now' to a
> > timestamp…
> The first STRFTIME() does run for every row, because timestamp is
> different for every row.  But the second STRFTIME() should be factored
> out and run only once.  Please verify that this is happening by
> looking at the output of EXPLAIN, and report the problem to me if you
> find out otherwise.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: calculation with the result of two select results

wmertens
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, [hidden email] <[hidden email]> wrote:

> 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 </home/.../daily_status.sql
> This happens always shortly after the beginning of a minute, so the risk
> that the both 'now' deliver different results is very low.
>
> Regards Matth
>
> Output of EXPLAIN:
> sqlite> .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 <[hidden email]> 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 convert the 'now' to a
>> > timestamp…
>> The first STRFTIME() does run for every row, because timestamp is
>> different for every row.  But the second STRFTIME() should be factored
>> out and run only once.  Please verify that this is happening by
>> looking at the output of EXPLAIN, and report the problem to me if you
>> find out otherwise.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: calculation with the result of two select results

Keith Medcalf
In reply to this post by nitpilot

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.

(and assuming that you always collect data on or after the "turn of the minute" and before the next following "turn of the minute")

Finding all the "matching" records is as easy as:

select C.minutestamp as currentminute,
       C.value       as currentvalue,
       Y.minutestamp as yesterdayminute,
       Y.Value       as yesterdayvalue
  from werte as C
  join werte as Y
 where Y.minutestamp = C.minutestamp - 1440;

with appropriate constraints such as order and limit and/or other conditionals ...

(for example to find the top minute and the predecessor day value, append "order by C.Minutestamp DESC limit 1" to the query.

Unless you happen to use the database at all times in a jurisdiction that is not subject to the political whims of the day fiddling with 'localtime', you want to store GMT/UT1 (often whimsically referred to as UTC) offsets in the database ... otherwise you will end up with "duplicated" stamps and "skipped" stamps on a periodic basis (once each per year for most jurisdictions, twice each per year for some jurisdictions, and three or more times a year for some others).  Of course, if you are running entirely and only on a platform for which the kernel localtime function uses proper tzdata translation (and those tables are kept up-to-date) then you are probably OK, except that none of the sqlite3 timestamp builtins record the offset from GMT/UT1, so unless you store only GMT/UT1 there is no way to actually know (or determine) the actual "instant time" ...

If you do need to take the political whims of localtime into account, then you will have to also store the "current political whim" flag along with the minutestamp and use it to modify the results ... (which can be a nightmare especially if you want to do it at the SQL level rather than the application level).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of [hidden email]
>Sent: Monday, 30 October, 2017 10:51
>To: SQLite mailing list
>Subject: Re: [sqlite] calculation with the result of two select
>results
>
>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 </home/.../daily_status.sql
>This happens always shortly after the beginning of a minute, so the
>risk
>that the both 'now' deliver different results is very low.
>
>Regards Matth
>
>Output of EXPLAIN:
>sqlite> .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 <[hidden email]> 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 convert the 'now' to a
>> > timestamp…
>> The first STRFTIME() does run for every row, because timestamp is
>> different for every row.  But the second STRFTIME() should be
>factored
>> out and run only once.  Please verify that this is happening by
>> looking at the output of EXPLAIN, and report the problem to me if
>you
>> find out otherwise.
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users