sqlite3 date operations when joining two tables in a view?
Posted
by duncan
on Stack Overflow
See other posts from Stack Overflow
or by duncan
Published on 2010-05-01T18:23:23Z
Indexed on
2010/05/01
18:27 UTC
Read the original article
Hit count: 219
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.
© Stack Overflow or respective owner