Invalid input syntax for type interval

Question

I am encountering an issue where I am not able to add intervals to a date in postgresql. In MySql I would do this:

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

with the corresponding statement in postgresql being:

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

But, when I do so I get an error of:

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

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

The simplified query:

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 cast your string as a date, that will resolve the problem:

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

The string on its own is ambiguous as there are several different types to represent time.