support make_interval function

[liukun4515]

          Do we have any method to convert or cast the integer type/integer expr with the timeunit to the interval?

@waitingkuo @alamb

I want to implement a app or function like this datetime + integer with timeunit, and the timeunit may be year,month,day.

I can't find the any method to convert the integer with timeunit to the interval, but in the PG which has a function make_interval() https://www.postgresql.org/docs/current/functions-datetime.html can do this.

I just can find a way to resolve the issue by using the concat method.

For example: I have a table like below, and want to convert the b with dayunit to interval

❯ \d test
+---------------+--------------+------------+-------------+-----------+-------------+
| table_catalog | table_schema | table_name | column_name | data_type | is_nullable |
+---------------+--------------+------------+-------------+-----------+-------------+
| datafusion    | public       | test       | a           | Date32    | NO          |
| datafusion    | public       | test       | b           | Int64     | NO          |
+---------------+--------------+------------+-------------+-----------+-------------+
2 rows in set. Query took 0.013 seconds.

Using the concat to get the utf8 expr which can be casted to interval.

❯   select cast(concat(b,' day') as interval), b from test;
+-------------------------------------------------------+---+
| concat(test.b,Utf8(" day"))                           | b |
+-------------------------------------------------------+---+
| 0 years 0 mons 2 days 0 hours 0 mins 0.000000000 secs | 2 |
+-------------------------------------------------------+---+

Originally posted by @liukun4515 in #3148 (comment)

[liukun4515]

cc @alamb @waitingkuo please help to add this issue to #3148

[waitingkuo]

@liukun4515
added, thank you

[waitingkuo]

hi @liukun4515

Do you plan to support multiple time unit? for example column a as year, column b as month

[alamb]

I adding the make_interval function seems very reasonable to me.

arrow knows how to parse strings like 5 days 4 minutes so you could build up such a string using concat. However, if you already have the fields and you know what output interval type you want/need doing it with a specific function would likely be much more performant

[Tangruilin]

@alamb Maybe you can assign it to me.

I can help with this issue

[milevin]

Is anybody working on this? cc: @alamb

I could really use it in the work that I am doing. (Supporting interval multiplication/division by integers; supporting Date32 + integer).

If nobody's on it, I can take a stab

[alamb]

FWIW DataFusion already does support interval syntax (though not make_interval):

> select '2021-01-01'::timestamp + interval '1 year';
+-----------------------------------------------------------------------------------------------------------+
| Utf8("2021-01-01") + IntervalMonthDayNano("IntervalMonthDayNano { months: 12, days: 0, nanoseconds: 0 }") |
+-----------------------------------------------------------------------------------------------------------+
| 2022-01-01T00:00:00                                                                                       |
+-----------------------------------------------------------------------------------------------------------+
1 row(s) fetched.
Elapsed 0.045 seconds.

The cast workaround works ok too

[milevin]

FWIW DataFusion already does support interval syntax

This is insufficient. Suppose we want to do something like this:

select make_interval(x, 'days') from (select 1 as x)

This wouldn't be representable using the interval syntax.

Why do I want to have it? I want to be able to write this, which works in Postgres:

select x * y as z from (select 5 as x, interval '2' day as y)

My current thinking is to desugar it into this:

select make_interval('month', x * date_part('month', y)) + make_interval('day', x * date_part('day', y)) + make_interval('nanosecond', x * date_part('nanosecond', y)) as z from (select 5 as x, interval '2' day as y)

Does this make sense? It's a little clunky, but that's my current working plan.

If somebody has a better idea, speak now or forever hold.

[findepi]

There is no reason for interval * integer syntax not to be supported. And once it's supported, not sure we need a new make_interval function.

Therefore I propose we re-purpose this issue to support interval * integer operator.

[alamb]

There is no reason for interval * integer syntax not to be supported. And once it's supported, not sure we need a new make_interval function.

Agreed

Therefore I propose we re-purpose this issue to support interval * integer operator.

I thought it might be clearer if we filed a new ticket with a clear description of the feature, so I did so in:

• Support multiply and divide on intervals #13850

Thus closing this ticket as not planned. Please reopen or comment if you disagree