Repository navigation
support make_interval function #6951
Description
Do we have any method to convert or cast the integer type/integer expr with the timeunit to the interval?
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)
Metadata
Metadata
Assignees
Labels
No labels
Activity
liukun4515 commentedon Jul 13, 2023
cc @alamb @waitingkuo please help to add this issue to #3148
waitingkuo commentedon Jul 13, 2023
@liukun4515
added, thank you
waitingkuo commentedon Jul 13, 2023
hi @liukun4515
Do you plan to support multiple time unit? for example column
aas year, columnbas monthalamb commentedon Jul 13, 2023
I adding the
make_intervalfunction seems very reasonable to me.arrow knows how to parse strings like
5 days 4 minutesso you could build up such a string usingconcat. 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 performantTangruilin commentedon Jan 29, 2024
@alamb Maybe you can assign it to me.
I can help with this issue
milevin commentedon Dec 14, 2024
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 commentedon Dec 15, 2024
FWIW DataFusion already does support interval syntax (though not
make_interval):The cast workaround works ok too
milevin commentedon Dec 16, 2024
This is insufficient. Suppose we want to do something like this:
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:
My current thinking is to desugar it into this:
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 commentedon Dec 17, 2024
There is no reason for
interval * integersyntax not to be supported. And once it's supported, not sure we need a newmake_intervalfunction.Therefore I propose we re-purpose this issue to support
interval * integeroperator.alamb commentedon Dec 19, 2024
Agreed
I thought it might be clearer if we filed a new ticket with a clear description of the feature, so I did so in:
Thus closing this ticket as not planned. Please reopen or comment if you disagree