Adding days and time to a timestamp in MySQL -

i have 3 fields in table: start_time: timestamp, days_prior: int, , time_open: time. want retrieve value of start_time - days, set time portion of result 00:00:00 , add time that.

that is, original timestamp might '2013-05-02 14:57:00' , days 1, time '09:30:00' , want return '2013-05-01 09:30:00'.

i realize can done splitting out date portion of timestamp, , concatenating time. however, if wanted use result in same query part of statement, need result of concatenation timestamp well.

my query, ideally, following:

select     t1.id,      t1.start_time,     concat(date_sub(date(t1.start_time), interval t2.days_prior day), ' ', t2.time_open)      t1, t2     t1.t2_id = t2.id 

this, however, producing results looking like:

1, 2012-10-30 18:00:00, 323031322d31302d33302030393a30303a3030 

i try avoid string operation concat when dealing dates , times. there proper date , time func handle instead.

try if works you. gives real datetime if need format presentation need add that.

select     t1.id,      t1.start_time,     makedate(year(t1.start_time), dayofyear(t1.start_time)) - interval t2.days_prior day + interval t.2time_open hour_second     t1, t2     t1.t2_id = t2.id 

sqlfiddle