support: Date +/plus Int or date_add function

[liukun4515]

Is your feature request related to a problem or challenge?

we have requirements related to the date operation, such as date_add, date_sub.

In the spark and mysql, the date_add(start_date, value_expr) function has been supported.
But in the datafusion, we only use the expr + interval 'value' day to support them.

In the mysql or the spark, the second args can be the expr, but the datafusion has no method to get the same target.

Describe the solution you'd like

In the PG document, PG support date +/plus integer.

We can try to support this feature in the datafusion or in the kernel of arrow-rs.

Describe alternatives you've considered

No response

Additional context

cc @waitingkuo @alamb

[alamb]

👍 -- I believe @tustvold is cleaning up the arithmetic logic in arrow-rs / datafusion now

What types can the value_expr be in spark?

[watfordkcf]

It takes integers:

spark-sql (default)> SELECT date_add(CAST(now() AS DATE), 1.5);
[DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] Cannot resolve "date_add(CAST(now() AS DATE), 1.5)" due to data type mismatch:
    Parameter 2 requires the ("INT" or "SMALLINT" or "TINYINT") type, however "1.5" has the type "DECIMAL(2,1)".; line 1 pos 7;
'Project [unresolvedalias(date_add(cast(now() as date), 1.5), None)]
+- OneRowRelation

[alamb]

https://spark.apache.org/docs/2.3.0/api/sql/#date_add

It appears that date_add should take the number of days as the second argument

To model this in DataFusion you could probably do a rewrite like this

date_add(x, y)

cast(x as date) + cast(y as IntervalMonthDayNano)

You could also add a new built in function (which would probably make the type casting logic more explicit)

[liukun4515]

👍 -- I believe @tustvold is cleaning up the arithmetic logic in arrow-rs / datafusion now

Ok, I will take look this work and track this process of work

What types can the value_expr be in spark?

In the spark

spark-sql> select version();
3.2.0 5d45a415f3a29898d92380380cfd82bfc7f579ea
Time taken: 0.084 seconds, Fetched 1 row(s)

spark-sql> desc test;
a                   	date
b                   	int

date + integer constant

spark-sql> explain extended select a+10 from test;
== Parsed Logical Plan ==
'Project [unresolvedalias(('a + 10), None)]
+- 'UnresolvedRelation [test], [], false

== Analyzed Logical Plan ==
date_add(a, 10): date
Project [date_add(a#49, 10) AS date_add(a, 10)#51]
+- SubqueryAlias spark_catalog.default.test
   +- HiveTableRelation [`default`.`test`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Data Cols: [a#49, b#50], Partition Cols: []]

== Optimized Logical Plan ==
Project [date_add(a#49, 10) AS date_add(a, 10)#51]
+- HiveTableRelation [`default`.`test`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Data Cols: [a#49, b#50], Partition Cols: []]

== Physical Plan ==
*(1) Project [date_add(a#49, 10) AS date_add(a, 10)#51]
+- Scan hive default.test [a#49], HiveTableRelation [`default`.`test`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Data Cols: [a#49, b#50], Partition Cols: []]

Time taken: 0.04 seconds, Fetched 1 row(s)

There is a specific analyse rule to handle the data/time with the operation of arithmetic, the rule convert the date + integer to the build-in function date_add.

date +/date_add integer column/expr

spark-sql> explain extended select a+b from test;
== Parsed Logical Plan ==
'Project [unresolvedalias(('a + 'b), None)]
+- 'UnresolvedRelation [test], [], false

== Analyzed Logical Plan ==
date_add(a, b): date
Project [date_add(a#88, b#89) AS date_add(a, b)#90]
+- SubqueryAlias spark_catalog.default.test
   +- HiveTableRelation [`default`.`test`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Data Cols: [a#88, b#89], Partition Cols: []]

== Optimized Logical Plan ==
Project [date_add(a#88, b#89) AS date_add(a, b)#90]
+- HiveTableRelation [`default`.`test`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Data Cols: [a#88, b#89], Partition Cols: []]

== Physical Plan ==
*(1) Project [date_add(a#88, b#89) AS date_add(a, b)#90]
+- Scan hive default.test [a#88, b#89], HiveTableRelation [`default`.`test`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Data Cols: [a#88, b#89], Partition Cols: []]

Time taken: 0.035 seconds, Fetched 1 row(s)

because the PG support the operation date +/- integer described in the doc https://www.postgresql.org/docs/current/functions-datetime.html
For example

date + integer → date

Add a number of days to a date

date '2001-09-28' + 7 → 2001-10-05

So I want to support the more arithmetic operation for date/time/timestamp/interval in the datafusion(maybe we can implement them in the arrow-rs).

The date operated by the arithmetic operation is required in the sql system or the query engine, So i don't know if the implementation of above operation in the arrow-rs kernel is suitable?

[tustvold]

Upstream will, in the next release, support date subtraction returning a duration in addition to adding durations or intervals to dates. I think the semantics of adding plain integers to dates is suitably opaque that we probably won't support that upstream, but the approach of using a rewrite to convert to a duration makes a lot of sense

[watfordkcf]

Postgres has a function make_interval:

make_interval ( [ years int [, months int [, weeks int [, days int [, hours int [, mins int [, secs double precision ]]]]]]] ) → interval

Create interval from years, months, weeks, days, hours, minutes and seconds fields, each of which can default to zero

make_interval(days => 10) → 10 days

Would this help if ported @liukun4515 ?

[liukun4515]

Upstream will, in the next release, support date subtraction returning a duration in addition to adding durations or intervals to dates.

What‘s the upstream? arrow-rs?

I think the semantics of adding plain integers to dates is suitably opaque that we probably won't support that upstream, but the approach of using a rewrite to convert to a duration makes a lot of sense

I also think the semantics of adding plain integers to dates is not suitable for adding in the arrow-rs.

The pg https://www.postgresql.org/docs/current/functions-datetime.html support the operation

date + integer → date

Add a number of days to a date

date '2001-09-28' + 7 → 2001-10-05

We can use the rewrite to support them.

Thanks @tustvold

[liukun4515]

Would this help if ported

Yes, i have a plan to implement this function from below discussion #3148 (comment)

and create issue to track this #6951

[DanCodedThis]

Hello, I have implemented date_add as a scalar UDF (also date_diff) akin to Snowflake spec. My company would like me to contribute to Datafusion (if it's needed). Does Datafusion need something like this? Perhaps it's the wrong issue to start this discussion. Do I need to open a different issue?

date_add spec: https://docs.snowflake.com/en/sql-reference/functions/dateadd
date_diff spec: https://docs.snowflake.com/en/sql-reference/functions/datediff

[edmondop]

@DanCodedThis and @alamb I looked at snowflake documentation, Spark documentation and postgres documentation

• Postgres provide a generic API that's based on Intervals
• Snowflake provides a simpler API where you provide the interval as a combination of timeUnit * number of units
• Spark supports only days as a timeunit

Implementing the Postgres approach seems the most flexible solution, and then we can implement a Snowflake dialect maybe to parse a snowflake-like UDF invocation into the Postgres-style?

[DanCodedThis]

@edmondop I think I agree with your statement.

• Also, does the new TypeSignatureClass + logical_string() allow parsing month as not a column/keyword, but as Utf8 without the single quotes? In our own implementation, there is a preprocessing that fixes that in a hacky way. If we later add the Snowflake way to DF, I don't think I will be able to reproduce it here.
• I saw the new date_part two weeks/week and a half ago on the Apache docs, and they were using month not 'month' in the example, perhaps it was a mistake in the docs that was fixed, or I didn't perceive it correctly. I could swear I saw it.
• I tried doing something similar for the aforementioned date_diff with the hope of later adding it to the date_add also, but the example that I was referring too was gone by that time I wrote it and tested it. (didn't work)