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-
>boun...@mailinglists.sqlite.org] On Behalf Of nitpi...@arcor.de
>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 <wout.mert...@gmail.com> 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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
