MaxGekk commented on issue #26034: [SPARK-29364][SQL] Return an interval from date subtract according to SQL standard URL: https://github.com/apache/spark/pull/26034#issuecomment-538781278 > If a popular DBMS like PostgreSQL ignores that SQL ANSI standard, why do we need to respect that? I haven't found any DBMS that return interval for date subtract so far. > we are trying to improve our SQL user UX. This is more interesting. Let's look at an use case: an user needs to find the difference between 2 date columns and add it to a timestamp column. Now it is hard to do that because date subtract returns an integer, but timestamp +/- accepts only intervals: ```sql spark-sql> select timestamp'now' + (date'2019-10-01' - date'2019-09-15'); Error in query: cannot resolve '(TIMESTAMP('2019-10-06 22:42:28.374') + datediff(DATE '2019-10-01', DATE '2019-09-15'))' due to data type mismatch: differing types in '(TIMESTAMP('2019-10-06 22:42:28.374') + datediff(DATE '2019-10-01', DATE '2019-09-15'))' (timestamp and int).; line 1 pos 7; 'Project [unresolvedalias((1570390948374000 + datediff(18170, 18154)), None)] +- OneRowRelation ``` and we cannot cast `INT` to `INTERVAL`: ```sql spark-sql> select timestamp'now' + cast(date'2019-10-01' - date'2019-09-15' as interval); Error in query: cannot resolve 'CAST(datediff(DATE '2019-10-01', DATE '2019-09-15') AS INTERVAL)' due to data type mismatch: cannot cast int to interval; line 1 pos 24; 'Project [unresolvedalias((1570391177861000 + cast(datediff(18170, 18154) as interval)), None)] +- OneRowRelation ```
---------------------------------------------------------------- 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]
