is there a way to get a result similar to `datediff` function

[jiangzhx]

Is your feature request related to a problem or challenge?

I’d like to ask if there is a way to get a result similar to datediff function , also it seems that this function is not available in DataFusion.

i try another way like this,but how can i get 6 days from interval result

select to_timestamp(now())-to_timestamp('2023-07-20T05:44:00.419557');

+------------------------------------------------------------------------+                                                                                             
| to_timestamp(now()) - to_timestamp(Utf8("2023-07-20T05:44:00.419557")) |                                                                                             
+------------------------------------------------------------------------+                                                                                             
| 0 years 0 mons 6 days 0 hours 51 mins 7.343851000 secs                 |                                                                                             
+------------------------------------------------------------------------+           

Describe the solution you'd like

something like date_diff

Describe alternatives you've considered

add udf like date_diff
or
make
select extract(day from interval '1 days' );
work

Error during planning: No function matches the given name and argument types 'date_part(Utf8, Interval(MonthDayNano))'. You might need to add explicit type casts.
        Candidate functions:
        date_part(Utf8, Date32)
        date_part(Utf8, Date64)
        date_part(Utf8, Timestamp(Second, None))
        date_part(Utf8, Timestamp(Microsecond, None))
        date_part(Utf8, Timestamp(Millisecond, None))
        date_part(Utf8, Timestamp(Nanosecond, None))
        date_part(Utf8, Timestamp(Nanosecond, Some("+00:00")))

Additional context

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

https://github.com/apache/arrow-datafusion/blob/52cf58b46133d448e067455baab0faf8a50e565a/datafusion/expr/src/built_in_function.rs#L1021-L1032

https://github.com/apache/arrow-datafusion/blob/52cf58b46133d448e067455baab0faf8a50e565a/datafusion/physical-expr/src/datetime_expressions.rs#L729C1-L729C1

[alamb]

I agree that adding support for for intervals to EXTRACT would be very helpful.

[alamb]

I think this would be a good first issue as the wiring is all done and this would be a relatively simple extension to date_part I think

[alamb]

For example, either use or follow https://docs.rs/arrow/latest/arrow/compute/fn.day.html

[smallzhongfeng]

Do we have plans to support date_diff, date_add and other time functions, @alamb such as comparing spark's udf function about date? https://spark.apache.org/docs/3.4.1/api/sql/search.html?q=date

[alamb]

Do we have plans to support date_diff, date_add and other time functions, @alamb such as comparing spark's udf

There do not appear to be any such plans: https://github.com/apache/arrow-datafusion/issues?q=is%3Aissue+is%3Aopen+date_diff+

Adding them seems like a good idea to me. However, st some point we probably need to figure out how to split functions into packages so users can mix/match "packages" of functions they want without having to get all of the others. I filed #7110 to track this idea

🤔

[NiwakaDev]

@alamb

I'd like to implement the following feature:

select extract(~ from interval ~ ); 

[alamb]

Thanks @NiwakaDev -- let us know if you run into any trouble.

[NiwakaDev]

As far as I understand, it seems like this issue depends on apache/arrow-rs#4969.

[alamb]

@NiwakaDev I am not sure about this.

Note DataFusion already supports extract for several field types:
https://arrow.apache.org/datafusion/user-guide/sql/scalar_functions.html#extract

What would extract (.. from interval ..) do? Perhaps you could provide an example or a link to another database system that implements such a function?

[Jefffrey]

Note there is an existing issue for implementing extract(X from interval) in Datafusion here: #6327

I've also put details in a comment there with comparison of other engines behaviour