Did you find any relevant Jira cases? They are often useful for understanding the background and intended semantics.
> On Sep 6, 2023, at 5:38 PM, <[email protected]> <[email protected]> wrote: > > 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 >
