Invalid input syntax for type interval

Question

I ran into a problem when I cannot add intervals to a date in postgresql. In MySql, I would do the following:

date_add('2015-02-02', interval -46 day) 

with the corresponding statement in postgresql:

 '2015-02-02' + -46 * interval '1 day' 

But, when I do this, I get an error message:

 [Err] ERROR: invalid input syntax for type interval: "2015-02-02" 

If I delete the + -46 * interval '1 day' section of the instruction, it works as expected.

Simplified request:

 SELECT cd.date_sort_prequeue_start AS date, SUM(CASE WHEN cd.call_conclusion='Answered' THEN 1 ELSE 0 END) AS calls_answered FROM data_warehouse.call_detail AS cd INNER JOIN data_warehouse.users_history AS uh ON cd.users_history_id=uh.id WHERE cd.date_sort_prequeue_start>= '2015-02-02' + (-46 * INTERVAL '1 day') AND cd.date_sort_prequeue_start<'2015-02-02' AND cd.call_type='I' AND uh.team='TeamOne' GROUP BY cd.date_sort_prequeue_start ORDER BY cd.date_sort_prequeue_start; 

Answer 1

If you specify your string as a date, this will solve the problem:

 '2015-02-02'::date + -46 * interval '1 day' 

The line itself is ambiguous, because there are several different types for representing time.

Answer 2

A simpler way to do this:

 date '2015-02-02' - interval '46 days'