Let's say I am doing similar query (where due_date is of type date) :
SELECT
(due_date + (7 * INTERVAL '1 DAY')) AS due_date_mod
FROM test_table
The resulting due_date_mod is type timestamp.
This makes sense as the result of the operation should be one type regardless of specific values and interval can have hours/minutes/seconds.
But is there a way to add days/months/years to a date without the result being time stamp and also obviously without casting? Or is casting the only way?
I know I can add days by using:
SELECT
(due_date + INTEGER '7') AS due_date_mod
And the result is type date.
But can I do something similar for months or years (without converting them to days)?
EDIT:
There seems to be no solution satisfying the requirements of the question. Proper way to get the required results is in the marked answer.
You already found that you can add integer to date to add days, returning date:
SELECT due_date + 7 AS due_date_mod -- type date
FROM test_table;
(Since date is an integer quantity inside, it's dead simple and cheap to add integer to it.)
As you can see in the table Date/Time Operators in the manual, there is no other operator that would return date (and no function, either).
And the unit "months" must be interpreted relative to the left operand (as months differ in their number of days). So you cannot simply extract the number of days from your months. You must use date + interval to get it right. Just add a cast, it's simple and very cheap:
SELECT (due_date + interval '3 months')::date AS due_date_mod
FROM test_table;
You are aware that (7 * interval '1 mon') = interval '7 mon', right?