I am looking at the TIMESTAMPDIFF function and every SQL dialect seems to have a slightly different definition, e.g.:
Databricks: https://docs.databricks.com/en/sql/language-manual/functions/timestampdiff.h tml seems to round after computing the result: One month is considered elapsed when the calendar month has increased and the calendar day and time is equal or greater to the start. Weeks, quarters, and years follow from that. Snowflake: https://docs.snowflake.com/en/sql-reference/functions/timestampdiff seems to round before computing the result: The unit (e.g. month) used to calculate the difference determines which parts of the DATE, TIME, or TIMESTAMP field are used to determine the result and thus determines the precision of the result. Smaller units are not used, so values are not rounded. For example, even though the difference between January 1, 2021 and February 28, 2021 is closer to two months than to one month, the following returns one month: The Calcite documentation does not spell out the semantics clearly. Moreover I took some tests from MySQL https://github.com/mysql/mysql-server/blob/ea1efa9822d81044b726aab20c857d5e1 b7e046a/mysql-test/r/func_time.result#L715 and one of them fails: select timestampdiff(month, DATE '2004-02-29', DATE '2005-02-28') MySQL returns 11, while Calcite returns 12 (e.g., when writing a SqlOperatorTest). Interestingly, all other MySQL tests I tried passed, so this is suspicious. The implementation in StandardConvertletTable seems to implement in one function both the "standard" TIMESTAMPDIFF and the Big Query TIMESTAMP_DIFF, so it's not particularly easy to read. Is this the expected result, or should I file an issue? Thank you, Mihai
