That will work.
If you run the query in the CLI prefixed with explain (after having given the
.explain command), you will see the actual code that the query engine executes.
You will note that where it accesses strftime('%s', 'now') it is surrounded by
a ONCE instruction, conditionally jumping over the recomputation if it has
already been computed once ...
13 Once 1 21 0 00
14 Null 0 13 0 00
15 Integer 1 14 0 00
16 String8 0 16 0 %s 00
17 String8 0 17 0 now 00
18 Function 3 16 15 strftime(-1) 02
19 Move 15 13 1 00
20 IfZero 14 21 -1 00
21 Gt 13 27 11 collseq(BINARY) 6c
This compares to your original select, which did not use a scalar subquery,
which had the code:
13 String8 0 13 0 %s 00
14 String8 0 14 0 now 00
15 Function 3 13 12 strftime(-1) 02
16 Gt 12 22 11 collseq(BINARY) 6c
17 Column 2 0 15 00
Which computed the value for each row, instead of just once for the entire
query ...
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Kevin Martin
> Sent: Sunday, 22 July, 2012 15:32
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Current Time in WHEN clause.
>
> For now I've decided to play safe and I've added a added an 'ok' column to
> tell me if I'm within 5s of an update.
>
> > CREATE VIEW near_update as select count(*) as n from aliases where
> abs(strftime('%s', 'now') - validFrom) < 5;
> > CREATE VIEW alias_info as select a.name, s.url, s.type, nu.n == 0 as ok
> from latest_aliases as a join services as s on a.assignedTo = s.name join
> near_update as nu;
>
> In the scripts, I just need to check the ok column, and if it's 0, then
> return a temporary error to the client.
>
> Probably not the nicest solution, but it keeps complicated sql out of the
> scripts, and makes sure all the services reported back are always in sync -
> which are my main aims. Plus, there are only likely to be a few updates a
> month, most in the middle of the night, so the occasional temporary error
> won't be a problem.
>
> On 22 Jul 2012, at 21:54, Keith Medcalf wrote:
>
> > if they are tiny, then it matters not, if they will always be tiny
>
>
> Yeah, we're not anticipating many updates, and eventually, I intend writing a
> cron script to clear the old ones out.
>
> Thanks,
> Kevin
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
