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 <nitpi...@arcor.de> 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 <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
