The following issues discuss the semantics of TIMESTAMPDIFF: https://issues.apache.org/jira/browse/CALCITE-1827, https://issues.apache.org/jira/browse/CALCITE-3529, https://issues.apache.org/jira/browse/CALCITE-1124. They all mention MySQL as a reference for this function. This would suggest that this is a bug. I will file a new Jira issue.
Mihai -----Original Message----- From: Julian Hyde Sent: Wednesday, September 06, 2023 5:42 PM To: [email protected] Subject: Re: TIMESTAMPDIFF semantics 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/timestamp > diff.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/ea1efa9822d81044b726aab20c8 > 57d5e1 > 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 >
