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
