Date arithmetic on values of type DATE without resulting in TIMESTAMP

Question

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.

Answer 1

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?

Answer 2

Originally I misinterpreted the question so what is below is examples (including output) of what @mcc had already discovered. I'll leave this answer here as they may be useful to others looking for examples of date, timestamp, and interval arithmetic.

Adding a date to an interval:

select '2021-08-11 12:23:00'::date + '2 months'::interval;
      ?column?       
---------------------
 2021-09-11 00:00:00

Or, if the field is a timestamp you can to cast it to date, but you still get a datetime answer:

select ('2021-08-11 12:23:00'::timestamp)::date + '2 years'::interval;
      ?column?       
---------------------
 2023-08-11 00:00:00

As the above examples show, when adding an interval to a date or timestamp the results is always a timestamp. If you require the results to be a date you can cast after adding the interval:

select ('2021-08-11 12:23:00'::timestamp + '60 days'::interval)::date;
    date    
------------
 2021-10-10

I've used intervals of days, months, and years to demonstrate how each time period can be added.

Answer 3

reference: https://www.postgresql.org/docs/current/catalog-pg-operator.html

SELECT
    oprname,
    p1.typname AS left_type,
    p2.typname AS right_type,
    p3.typname AS result_type,
    oprcode
FROM
    pg_operator po
    JOIN pg_type p1 ON p1.oid = po.oprleft
    JOIN pg_type p2 ON p2.oid = po.oprright
    JOIN pg_type p3 ON p3.oid = po.oprresult
WHERE
    oprname = '+'
    AND p1.typname = 'date'::name;

So there is a row like :

 oprname | left_type | right_type | result_type |     oprcode
---------+-----------+------------+-------------+------------------
 +       | date      | int4       | date        | date_pli

That's why select now()::date + 1; will work.

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?

"add days/months/years" Means:
Is there a operator that right side (or left side) is interval data type then the return is date data type.
Does it exist? we can query the catalog schema and find out.

SELECT
    oprname,
    p1.typname AS left_type,    --left side of a operator data type
    p2.typname AS right_type,   --right side of a operator data type
    p3.typname AS result_type,  --return data type
    oprcode
FROM
    pg_operator po
    JOIN pg_type p1 ON p1.oid = po.oprleft
    JOIN pg_type p2 ON p2.oid = po.oprright
    JOIN pg_type p3 ON p3.oid = po.oprresult
WHERE
    p1.typname = 'interval'::name
    OR p2.typname = 'interval'::name;

---

 oprname |  left_type  | right_type  | result_type |         oprcode
---------+-------------+-------------+-------------+-------------------------
 +       | date        | interval    | timestamp   | date_pl_interval
 -       | date        | interval    | timestamp   | date_mi_interval
 +       | timestamptz | interval    | timestamptz | timestamptz_pl_interval
 -       | timestamptz | interval    | timestamptz | timestamptz_mi_interval
 =       | interval    | interval    | bool        | interval_eq
 <>      | interval    | interval    | bool        | interval_ne
 <       | interval    | interval    | bool        | interval_lt
 <=      | interval    | interval    | bool        | interval_le
 >       | interval    | interval    | bool        | interval_gt
 >=      | interval    | interval    | bool        | interval_ge
 +       | interval    | interval    | interval    | interval_pl
 -       | interval    | interval    | interval    | interval_mi
 *       | interval    | float8      | interval    | interval_mul
 *       | float8      | interval    | interval    | mul_d_interval
 /       | interval    | float8      | interval    | interval_div
 +       | time        | interval    | time        | time_pl_interval
 -       | time        | interval    | time        | time_mi_interval
 +       | timetz      | interval    | timetz      | timetz_pl_interval
 -       | timetz      | interval    | timetz      | timetz_mi_interval
 +       | interval    | time        | time        | interval_pl_time
 +       | timestamp   | interval    | timestamp   | timestamp_pl_interval
 -       | timestamp   | interval    | timestamp   | timestamp_mi_interval
 +       | interval    | date        | timestamp   | interval_pl_date
 +       | interval    | timetz      | timetz      | interval_pl_timetz
 +       | interval    | timestamp   | timestamp   | interval_pl_timestamp
 +       | interval    | timestamptz | timestamptz | interval_pl_timestamptz
 <->     | interval    | interval    | interval    | interval_dist
(27 rows)

As you can see the query column return type is {timestamp,timestamptz, bool, interval,time,timetz}. So it cannot return date.