sqlite3 date operations when joining two tables in a view?
- by duncan
In short, how to add minutes to a datetime from an integer located in another table, in one select statement, by joining them?
I have a table P(int id, ..., int minutes)
and a table S(int id, int p_id, datetime start)
I want to generate a view that gives me PS(S.id, P.id, S.start + P.minutes) by joining S.p_id=P.id
The problem is, if I was generating the query from the application, I can do stuff like:
select datetime('2010-04-21 14:00', '+20 minutes');
2010-04-21 14:20:00
By creating the string '+20 minutes' in the application and then passing it to sqlite. However I can't find a way to create this string in the select itself:
select p.*,datetime(s.start_at, formatstring('+%s minutes', p.minutes)) from p,s where s.p_id=p.id;
Because sqlite as far the documentation tells, does not provide any string format function, nor can I see any alternative way of expressing the date modifiers.
