SQL: Extend DATE_TRUNC to also operate on intervals

[matriv]

According to the PostgreSQL spec the DATE_TRUNC's 2nd argument can also be an interval: https://www.postgresql.org/docs/9.1/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
Currently for ES-SQL it only supports DATETIME & DATE.

Follow up to: #46319

[elasticmachine]

Pinging @elastic/es-search

[musteaf]

Hi, I would like to work on this issue. Can I take it?

[rajalohiy8]

can I take this issue and work on this?

[musteaf]

Hi @matriv I am fully ready to work on this issue. I am just waiting for your response to prevent conflicts. Can I take it?

[matriv]

@musteaf Sorry for the delayed response. Please go ahead.
Thank you!

[musteaf]

@matriv No problem. I will start right away. Thank you.

[shoaib4330]

@musteaf Could I pick it up if you're not actively working on it?

[DANIELK121]

Hi, is this issue still open?
If so, I would like some help to run the function DATE_TRUNC -
I have forked and downloaded the project to my machine, and as written in the README file i ran
the @./gradlew assemble@ command in the cloned directory (via cmd line in windows).
I get the message:

FAILURE: Build failed with an exception.
and

* What went wrong:
A problem occurred evaluating project ':buildSrc'.
> At least Java 11 is required to build elasticsearch gradle tools

i have JDK 13 installed.
what can be the problem?

[musteaf]

Hi @shoaib4330 I'm about to finish. Only tests left.

[musteaf]

@matriv Sorry for being late. I sent a PR. #47720