Support ::interval casting / INTERVAL SQL datatype

[alamb]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.
In DataFusion, we support SQL syntax like INTERVAL '3 months' that is then converted into Intervals (I think originally contributed by @ovr).

This has a few limitations:

1. It can't represent the full range of intervals, leading to issues like date_bin doesn't work with months or years #5689
2. It only works for constant strings in INTERVAL ... not more generally

Describe the solution you'd like
I would like to be able to use the interval type from sql as normal:

select '1 day`::interval
select cast('1 day' as interval);
select arrow_cast('1 day', 'Interval(MonthDayNano)');

Here is what happens today

❯ select '1 day'::interval;
This feature is not implemented: Unsupported SQL type Interval
❯ select cast('1 day' as interval);
This feature is not implemented: Unsupported SQL type Interval
❯ select arrow_cast('1 day', 'Interval(MonthDayNano)');
Error during planning: Cannot automatically convert Utf8 to Interval(MonthDayNano)
❯ select arrow_cast('1 day', 'Interval(YearMonth)');
Error during planning: Cannot automatically convert Utf8 to Interval(YearMonth)
❯ select arrow_cast('1 day', 'Interval(DayTime)');
Error during planning: Cannot automatically convert Utf8 to Interval(DayTime)

Describe alternatives you've considered
One thing we need to figure out is what Arrow interval type will map to the sql interval type.

Intervals can be either YearMonth or DayTime or MonthDayNano

I think in order to support '1 day' and '1 minute' with the same type, we will need to use IntervalMonthDayNano:

https://docs.rs/arrow/35.0.0/arrow/datatypes/struct.IntervalMonthDayNanoType.html

Additional context
I believe the casting support is added upstream in arrow-rs by @doki23 -- apache/arrow-rs#3762 in arrow 35.0.0.

However, that upgrade is blocked (see #5441) so we may have to wait for arrow-36.0.0 to implement this feature

[alamb]

@waitingkuo I would be curious to know what you think about this proposal

[doki23]

I think in order to support '1 day' and '1 minute' with the same type, we will need to use IntervalMonthDayNano

To avoid data compatibility problem we must use IntervalMonthDayNano to store. But I think maybe we could provide some optional output formats like postgresql? Because directly displaying IntervalMonthDayNano is somewhat tediously long like 0 years 1 mons 0 days 0 hours 0 mins 0.000000000 secs if interval is only x months.

[alamb]

To avoid data compatibility problem we must use IntervalMonthDayNano to store. But I think maybe we could provide some optional output formats like postgresql?

I agree this would be a good idea.

Ideally we could add some options to the arrow display module to support different output formats. I plan to file some tickets about interval support shortly, and I will include more display as well

[waitingkuo]

hi @alamb this is great, arrow_cast to specific IntervalUnit is quite helpful.

I'd like to know when the source doesn't feed into the interval , e.g.

select arrow_cast('1 second', 'Interval(YearMonth)');

do we just simply return a 0 or we should raise some warning or even err?

or when there're some ambiguities

select arrow_cast('1 month', 'Interval(DayTime)');

do we return 30 days or we should raise an ambiguity error?

[alamb]

I'd like to know when the source doesn't feed into the interval , e.g.

select arrow_cast('1 second', 'Interval(YearMonth)');
do we just simply return a 0 or we should raise some warning or even err?

I think the best way to think of arrow_cast is as a way to invoke the arrow cast kernel

So in your examples

select arrow_cast('1 second', 'Interval(YearMonth)');

That would invoke cast(StringArray, IntervalYearMonthArray)

select arrow_cast('1 month', 'Interval(DayTime)');

Basically this should be whatever the arrow casting kernel does (probably throw an ambiguity error, but I am not sure)

[alamb]

After upgrade to arrow 36:

select arrow_cast('1 second', 'Interval(YearMonth)');DataFusion CLI v21.0.0
❯ select arrow_cast('1 second', 'Interval(YearMonth)');
Arrow error: Cast error: Cannot cast 1 second to IntervalYearMonth. Only year and month fields are allowed.
❯ select arrow_cast('1 month', 'Interval(DayTime)');

+--------------------------------------------------+
| Utf8("1 month")                                  |
+--------------------------------------------------+
| 0 years 0 mons 30 days 0 hours 0 mins 0.000 secs |
+--------------------------------------------------+

Here is a small PR that adds basic support for the SQL interval type: #5792