Is there a way to do date arithmetic on values of type DATE without result being of type TIMESTAMP?

user_13648567 0
Viewed 66

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.

date-arithmetic postgresql sql
2 Answers

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?

user_9398600

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.

user_61707980
Related