Re: [sqlite] Current Time in WHEN clause.

s-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

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 ==

Re: [sqlite] Current Time in WHEN clause.

Pavel, > No SQL format can guarantee anything. And changing SQL format won't change > its execution plan - SQL optimizer will decide that for you. Agreed. > And I don't know where did you take your information from but I can guarantee > you that with your query SQLite will execute strftime

Re: [sqlite] Current Time in WHEN clause.

Keith, No SQL format can guarantee anything. And changing SQL format won't change its execution plan - SQL optimizer will decide that for you. And I don't know where did you take your information from but I can guarantee you that with your query SQLite will execute strftime many times and OP's

Re: [sqlite] Current Time in WHEN clause.

select a.name, a.assignedTo, a.validFrom from aliases a, (select name, max(validfrom) as mvf from aliases, (select strftime('%s', 'now') as now) as c where validFrom <= now group by name) as b where a.name=b.name and a.validfrom=b.mvf; should

Re: [sqlite] Current Time in WHEN clause.

On 22 Jul 2012, at 6:48pm, Kevin Martin wrote: > Is the behaviour it will only run once guaranteed by any standard, or likely > to change in future/previous versions of sqlite? It's not predictable, even in the current version. SQLite can choose to re-prepare a prepared

Re: [sqlite] Current Time in WHEN clause.

On 22 Jul 2012, at 18:35, Keith Medcalf wrote: Thanks for that. I can't say I understand much of that output at the moment. I'm just reading http://www.sqlite.org/eqp.html to try and figure it out. Is the behaviour it will only run once guaranteed by any standard, or likely to change in

Re: [sqlite] Current Time in WHEN clause.

> > (select name, max(validfrom) as mvf from aliases where validFrom <= (select > > strftime('%s', 'now')) group by name) > I don't think that solves the problem because each time the group by query is > executed, your subquery will be executed again. Not possible. WHERE conditions are

Re: [sqlite] Current Time in WHEN clause.

On 22 Jul 2012, at 16:37, Pavel Ivanov wrote: > You can execute "select strftime('%s', 'now')" first and then put its > result into your query. It seems as though the opinion is that it may execute multiple times. > But that won't work with view, of course. I'd rather keep the alias_info

Re: [sqlite] Current Time in WHEN clause.

On 22 Jul 2012, at 17:06, Keith Medcalf wrote: > (select name, max(validfrom) as mvf from aliases where validFrom <= (select > strftime('%s', 'now')) group by name) I don't think that solves the problem because each time the group by query is executed, your subquery will be executed again.

Re: [sqlite] Current Time in WHEN clause.

If you want to make sure the strftime function is only called once why not say: CREATE VIEW latest_aliases as select a.name, a.assignedTo, a.validFrom from aliases as a join (select name, max(validfrom) as mvf from aliases where validFrom <= (select strftime('%s', 'now')) group by name) as b

Re: [sqlite] Current Time in WHEN clause.

> I'm trying to convince myself that the subquery in latest_aliases only > executes once, and also that alias_info only runs latest_aliases once. > However, I'm not doing very well at convincing myself. Can someone confirm > this is true, or suggest a nice solution to get the current time