postgresql - Postgres SQL update one table column based on calculation of the other table's field -

i have 2 tables following, want update date_table's total field corresponding time_table's sum(end - start) calculation result.

date_table id  name    date        total 1   xx      2016-02-01 2   yy      2016-02-02  time_table id  date_id start                   end 1   1       2016-02-01 08:00:00     2016-02-01 11:00:00  2   1       2016-02-01 14:00:00     2016-02-01 20:00:00  3   2       2016-02-02 08:00:00     2016-02-02 11:00:00  4   2       2016-02-02 14:00:00     2016-02-02 20:00:00  

so, result date_table first record total be:

(2016-02-01 11:00:00 - 2016-02-01 08:00:00) + (2016-02-01 20:00:00 - 2016-02-01 14:00:00) 

two table joined date_id

is like?

update date_table set date_table.total =  (   select      sum(time_table.end - time_table.start)    time_table         date_table.id = time_table.date_id ) 

not quite sure how postgres subtract datetime?

i assume data type of total field in date_table integer , expected output is

id name date       total  -- ---- ---------- -----  1  xx   2016-02-01 9      2  yy   2016-02-02 9    

so,

select dteend-dtestart col time_table; 

will produce output interval data type

i.e

col  interval      --------  03:00:00  06:00:00  03:00:00  06:00:00  

you cannot perform aggregate function on interval directly.following method can used interval in integer data type.

select date_id, sum(extract(epoch dteend-dtestart)/3600)::int time_table group date_id; 

output:

date_id sum  ------- ---  1       9    2       9    

and update should following.

update date_table set total = t.sum (     select date_id         ,sum(extract(epoch dteend - dtestart) / 3600)::int     time_table     group date_id     ) t date_table.id = t.date_id;