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 on a.name=b.name and a.validFrom=b.mvf; --- () 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 09:17 > To: General Discussion of SQLite Database > Subject: [sqlite] Current Time in WHEN clause. > > Hi, > > I have the following schema: > > > CREATE TABLE aliases (name text not null, assignedTo text, validFrom > integer not null); > > CREATE TABLE services (name text primary key, url text not null, type text > not null); > > 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 <= strftime('%s', 'now') group by name) as b on a.name=b.name and > a.validFrom=b.mvf; > > CREATE VIEW alias_info as select a.name, s.url, s.type from latest_aliases > as a join services as s on a.assignedTo = s.name; > > The aliases table maps names to services at different times. So for example > with the following data: > > > sqlite> select * from services; > > svc1|http://somewhere|type1 > > svc2|http://somewhere.else|type1 > > > sqlite> select *,datetime(validFrom, 'unixepoch') from aliases; > > env1|svc1|1342967110|2012-07-22 14:25:10 > > env1|svc2|1342967185|2012-07-22 14:26:25 > > I want env1 to be mapped to svc1 after 14:25:10, until 14:26:25 after which > point I want it to be mapped to svc2. > > This is done with the latest_aliases view, alias_info just joins > latest_aliases to the services table to get the connection info. > > However, I'm quite concerned about executing strftime('%s', 'now') inside the > subquery, specifically does it execute multiple times? I don't want this to > happen because it could cause a rare bug when the list of services returned > is partly pre and partly post an update. > > 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 function out > of the when clause. > > Thanks, > Kevin Martin. > _______________________________________________ > 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