MaxGekk opened a new pull request #26036: [SPARK-29365][SQL] Support dates and timestamps subtraction URL: https://github.com/apache/spark/pull/26036 ### What changes were proposed in this pull request? Added new rules to `TypeCoercion.DateTimeOperations` for the `Subtract` expression which is replaced by existing `TimestampDiff` expression if one of its parameter has the `DATE` type and another one is the `TIMESTAMP` type. The date argument is casted to timestamp. ### Why are the changes needed? - To maintain feature parity with PostgreSQL which supports subtraction of a date from a timestamp and a timestamp from a date: ```sql maxim=# select timestamp'now' - date'epoch'; ?column? ---------------------------- 18175 days 21:07:33.412875 (1 row) maxim=# select date'2020-01-01' - timestamp'now'; ?column? ------------------------- 86 days 02:52:00.945296 (1 row) ``` - To conform to the SQL standard which defines datetime subtraction as an interval. ### Does this PR introduce any user-facing change? Yes, currently the queries bellow fails with the error: ```sql spark-sql> select timestamp'now' - date'2019-10-01'; Error in query: cannot resolve '(TIMESTAMP('2019-10-06 21:05:07.234') - DATE '2019-10-01')' due to data type mismatch: differing types in '(TIMESTAMP('2019-10-06 21:05:07.234') - DATE '2019-10-01')' (timestamp and date).; line 1 pos 7; 'Project [unresolvedalias((1570385107234000 - 18170), None)] +- OneRowRelation ``` after the changes: ```sql spark-sql> select timestamp'now' - date'2019-10-01'; interval 5 days 21 hours 4 minutes 55 seconds 878 milliseconds ``` ### How was this patch tested? - Add new cases to the `rule for date/timestamp operations` test in `TypeCoercionSuite` - by 2 new test in `datetime.sql`
---------------------------------------------------------------- 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. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
