Add ability to add `INTERVAL` to DATE

[surister]

Problem Statement

We can do

SELECT CURRENT_DATE + 1

but not

SELECT CURRENT_DATE + INTERVAL '1 day';
UnsupportedFunctionException[Unknown function: (curdate() + cast('1 day' AS interval)), no overload found for matching argument types: (date, interval)

This is a feature that exists in postgres that Tableau uses. https://www.postgresql.org/docs/current/functions-datetime.html

Possible Solutions

No response

Considered Alternatives

No response

[mariakont]

Hi! My colleague @katerinarousi and I, would be interested to be assigned to the specific issue, as our first contribution to this project for our Software Engineering course assignment. Any further information for the issue would be appreciated. Thank you!

[matriv]

Hi, thx for your interest in contributing to CrateDB. You should adjust IntervalArithmeticFunctions and use IntervalFunctionTest for the tests. I'm assigning you to the issue, and let me know of any further help you'd might need.

[katerinarousi]

Hey! Moving forward with the implementation, we would like to confirm that our process of thought aligns with the concept of the issue. We understand that dates are represented as Unix timestamps. To solve the issue we plan to firstly convert the parsed interval to the corresponding milliseconds (Period to int), and then perform the addition operation between the two integer values. The function will then return the calculated timestamp. Any feedback or guidance would be very appreciated. Thank you!

[matriv]

Hi @katerinarousi, thx for working on this! You can use long (milliseconds to do the operations), but I think it's easier to convert the date to a DateTime object and use the DateTime.add() (and minus(), to implement the subtraction as well). See for example how this is done for Timestamps +/- INTERVAL: https://github.com/crate/crate/blob/master/server/src/main/java/io/crate/expression/scalar/arithmetic/IntervalTimestampArithmeticScalar.java#L137 https://github.com/crate/crate/blob/master/server/src/main/java/io/crate/expression/scalar/arithmetic/IntervalTimestampArithmeticScalar.java#L110