Add days to a date in PostgresSQL

Question

I need to add number of days to a column. Below is the code I need to modify:

DATE_TRUNC('day', a.targetdeliverydateforcalc) + COALESCE(a.to_transit_time_amount, 0) + 2

I tried this (and similar variations):

DATE_TRUNC('day', a.targetdeliverydateforcalc) + INTERVAL ||COALESCE(a.to_transit_time_amount, 0) + 2 || 'day')

Answer 1

Two ways come to mind to solve this. Computing the days then converting to an interval:

DATE_TRUNC('day', a.targetdeliverydateforcalc)::date + (COALESCE(a.to_transit_time_amount, 0) + 2)*interval '1 day'

or calculate the number of days and just add the resulting integer:

DATE_TRUNC('day', a.targetdeliverydateforcalc)::date + (COALESCE(a.to_transit_time_amount, 0) + 2)

Answer 2

You can also use the make_interval() function:

DATE_TRUNC('day', a.targetdeliverydateforcalc)
    + make_interval(days => COALESCE(a.to_transit_time_amount, 0) + 2)

Answer 3

I would usually express this as:

(DATE_TRUNC('day', a.targetdeliverydateforcalc) + 
 (COALESCE(a.to_transit_time_amount, 0) + 2) * INTERVAL '1 DAY'
)