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

Filed under:
|
|
|
|

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

Related posts about sqlite

Related posts about sqlite3