sqlite3 date operations when joining two tables in a view?

Question

In short, how to add minutes to a datetime from an integer located in another table, in one select statement, by joining them, in sqlite3?

I have a sqlite3 db with:

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.

In MySQL, the date modifiers are not based on strings, so it actually works:

mysql> create table p ( id integer, minutes integer);
mysql> create table s ( id integer, p_id integer, start datetime);
mysql> insert into p values (1, 10);
mysql> insert into p values (2, 15);
mysql> insert into s values (1, 1, '2008-12-31 14:00');
mysql> insert into s values (2, 1, '2008-12-31 15:00');
mysql> insert into s values (3, 2, '2008-05-10 13:30');
mysql> SELECT p.*,(s.start + INTERVAL p.minutes MINUTE) FROM p,s WHERE p.id=s.p_id;
+------+---------+---------------------------------------+
| id   | minutes | (s.start + INTERVAL p.minutes MINUTE) |
+------+---------+---------------------------------------+
|    1 |      10 | 2008-12-31 14:10:00                   | 
|    1 |      10 | 2008-12-31 15:10:00                   | 
|    2 |      15 | 2008-05-10 13:45:00                   | 
+------+---------+---------------------------------------+
3 rows in set (0.02 sec)

Answer 1

Why not just use concatenation?:

SELECT ('+' + 10 + ' minutes')

Answer 2

Concatenation did not work using +. However the || concatenation operand did work as expected.

So MySQL's

SELECT p.*,(s.start + INTERVAL p.minutes MINUTE) FROM p,s WHERE p.id=s.p_id;

Can be written in sqlite3 as:

select p.*, datetime(s.start, '+' || p.minutes || ' minutes') from p, s where s.p_id=p.id;

Which produces the correct answer. Thanks to newtover for pointing in the right direction.

1|10|2008-12-31 14:10:00
1|10|2008-12-31 15:10:00
2|15|2008-05-10 13:45:00