You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "liukun4515 (via GitHub)" <gi...@apache.org> on 2023/07/13 11:01:22 UTC

[GitHub] [arrow-datafusion] liukun4515 opened a new issue, #6951: support make_interval function

liukun4515 opened a new issue, #6951:
URL: https://github.com/apache/arrow-datafusion/issues/6951

                 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 `day`unit 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 https://github.com/apache/arrow-datafusion/issues/3148#issuecomment-1631812195_
               


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] support make_interval function [arrow-datafusion]

Posted by "Weijun-H (via GitHub)" <gi...@apache.org>.
Weijun-H commented on issue #6951:
URL: https://github.com/apache/arrow-datafusion/issues/6951#issuecomment-1857647471

   I am glad to take this ticket


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] support make_interval function [arrow-datafusion]

Posted by "Tangruilin (via GitHub)" <gi...@apache.org>.
Tangruilin commented on issue #6951:
URL: https://github.com/apache/arrow-datafusion/issues/6951#issuecomment-1914448129

   @alamb Maybe you can assign it to me.
   
   I can help with this issue


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] support make_interval function [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #6951:
URL: https://github.com/apache/datafusion/issues/6951#issuecomment-2555714906

   > 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:
   - https://github.com/apache/datafusion/issues/13850
   
   
   Thus closing this ticket as not planned. Please reopen or comment if you disagree


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


[GitHub] [arrow-datafusion] waitingkuo commented on issue #6951: support make_interval function

Posted by "waitingkuo (via GitHub)" <gi...@apache.org>.
waitingkuo commented on issue #6951:
URL: https://github.com/apache/arrow-datafusion/issues/6951#issuecomment-1634063621

   @liukun4515 
   added, thank you


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [arrow-datafusion] waitingkuo commented on issue #6951: support make_interval function

Posted by "waitingkuo (via GitHub)" <gi...@apache.org>.
waitingkuo commented on issue #6951:
URL: https://github.com/apache/arrow-datafusion/issues/6951#issuecomment-1634066403

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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] support make_interval function [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #6951: support make_interval function
URL: https://github.com/apache/datafusion/issues/6951


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


[GitHub] [arrow-datafusion] alamb commented on issue #6951: support make_interval function

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #6951:
URL: https://github.com/apache/arrow-datafusion/issues/6951#issuecomment-1634914465

   I think `make_interval` would be a find addition
   
   Note most of the hard work is already supported in arrow which can parse strings like `5 days 4 minutes`. 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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] support make_interval function [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #6951:
URL: https://github.com/apache/datafusion/issues/6951#issuecomment-2544120396

   FWIW DataFusion already does support interval syntax (though not `make_interval`):
   
   ```sql
   > 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
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] support make_interval function [datafusion]

Posted by "findepi (via GitHub)" <gi...@apache.org>.
findepi commented on issue #6951:
URL: https://github.com/apache/datafusion/issues/6951#issuecomment-2548141469

   
   
   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.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] support make_interval function [datafusion]

Posted by "milevin (via GitHub)" <gi...@apache.org>.
milevin commented on issue #6951:
URL: https://github.com/apache/datafusion/issues/6951#issuecomment-2543336098

   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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] support make_interval function [datafusion]

Posted by "milevin (via GitHub)" <gi...@apache.org>.
milevin commented on issue #6951:
URL: https://github.com/apache/datafusion/issues/6951#issuecomment-2546782678

   > 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.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


[GitHub] [arrow-datafusion] liukun4515 commented on issue #6951: support make_interval function

Posted by "liukun4515 (via GitHub)" <gi...@apache.org>.
liukun4515 commented on issue #6951:
URL: https://github.com/apache/arrow-datafusion/issues/6951#issuecomment-1634043251

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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org