Support interval mul / div double arithmetic

[Omega359]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.

As part of updating Datafusion to better support date / time math operations (apache/datafusion#19022 among others) it was uncovered that arrow-rs does not support interval * / number ops that should be valid.

For example:

Multiply an interval by a scalar

• interval '1 second' * 900 → 00:15:00
• interval '1 day' * 21 → 21 days
• interval '1 hour' * 3.5 → 03:30:00

Divide an interval by a scalar

• interval '1 hour' / 1.5 → 00:40:00

From https://www.postgresql.org/docs/current/functions-datetime.html

Describe the solution you'd like

Updated support for interval and scalar operations in arrow-arith

Describe alternatives you've considered

Additional context

[tustvold]

Multiplication I think is reasonable, division I can see causing some issues

In particular what is 1 day / 3? 0 day? Unfortunately a day is not consistently 24 hours courtesy of DST. Do we just error?

I also think we should probably only support Int64 and rely on query engines coercion machinery, otherwise we'll end up generating a load of code for what is a relatively niche use-case.

[Omega359]

Currently with df/arrow it looks like it's handled correctly:

> set timezone TO 'CET';
0 row(s) fetched. 
Elapsed 0.003 seconds.

> SELECT '2020-03-29 00:00:00 Europe/Prague'::timestamptz + INTERVAL '1' DAY, '2020-03-29 00:00:00 Europe/Prague'::timestamptz + INTERVAL '24' HOUR;
+---------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Utf8("2020-03-29 00:00:00 Europe/Prague") + IntervalMonthDayNano("IntervalMonthDayNano { months: 0, days: 1, nanoseconds: 0 }") | Utf8("2020-03-29 00:00:00 Europe/Prague") + IntervalMonthDayNano("IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 86400000000000 }") |
+---------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| 2020-03-30T00:00:00+02:00                                                                                                       | 2020-03-30T01:00:00+02:00                                                                                                                    |
+---------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row(s) fetched. 
Elapsed 0.005 seconds.

This aligns with postgresql:

When adding an interval value to (or subtracting an interval value from) a timestamp with time zone value, the days component advances (or decrements) the date of the timestamp with time zone by the indicated number of days. Across daylight saving time changes (with the session time zone set to a time zone that recognizes DST), this means interval '1 day' does not necessarily equal interval '24 hours'. For example, with the session time zone set to CST7CDT, timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' will produce timestamp with time zone '2005-04-03 12:00-06', while adding interval '24 hours' to the same initial timestamp with time zone produces timestamp with time zone '2005-04-03 13:00-06', as there is a change in daylight saving time at 2005-04-03 02:00 in time zone CST7CDT.

However, in this issue the interval isn't related to anything that has a timezone so I think that mapping a day to 24 hours is reasonable. I believe postgresql treats it exactly that way too:

> select interval '1 day' / 3;
----
08:00:00

[tustvold]

Currently with df/arrow it looks like it's handled correctly:

Yes, and a lot of effort has gone into making it work correctly

However, in this issue the interval isn't related to anything that has a timezone so I think that mapping a day to 24 hours is reasonable. I believe postgresql treats it exactly that way too:

The point is that an interval can be used with dates with timezones, so it is incorrect to treat days and hours as interchangeable. If you want that behaviour you should just use a duration type 😄

[Omega359]

When doing addition or subtraction I completely agree. However I don't think that is the case for mul and div as it's only applicably to this small feature - it makes no sense otherwise.