I think it is because there is no clear mapping for how to convert a month (and then same to year) in interval to seconds/milliseconds. Does a month have 30 days, 31 days, 28 days or 29 days? (Probably can find an answer in SQL standard).
-Rui On Sun, Jun 21, 2020 at 4:00 PM Rommel Quintanilla <rom...@blazingdb.com> wrote: > Hi, I'm investigating the behavior of the functions timestampdiff with > different time-units. > > I found that in the case of the time-units: second, minute, hour, and day, > the logical plan returned by Calcite is consistent. Since the input is a > timestamp in milliseconds, in the same way, the result is in milliseconds. > > For example, for the following query: > "select l_shipdate, l_commitdate, timestampdiff(DAY, l_commitdate, > l_shipdate) as diff from lineitem limit 5" > > its logical plan produced is: > > LogicalSort(fetch=[5]) > LogicalProject(l_shipdate=[$10], l_commitdate=[$11], > diff=[CAST(/INT(Reinterpret(-($10, $11)), 86400000)):INTEGER]) > LogicalTableScan(table=[[main, lineitem]]) > > So far, so good. However, for the month and year case, the output is not > what I would expect. For the query: > > "select l_shipdate, l_commitdate, timestampdiff(MONTH, l_commitdate, > l_shipdate) as diff from lineitem limit 5" > > its logical plan produced is: > > LogicalSort(fetch=[5]) > LogicalProject(l_shipdate=[$10], l_commitdate=[$11], > diff=[CAST(Reinterpret(-($10, $11))):INTEGER]) > LogicalTableScan(table=[[main, lineitem]]) > > What I expected is that the subtraction is also divided by a month in > milliseconds, something like: > LogicalProject(l_shipdate=[$10], l_commitdate=[$11], > [CAST(/INT(Reinterpret(-($10, $11)), 2592000000)):INTEGER]) > > Doesn't seem to be a bug in Calcite, because the processing of constants > in the unit tests passes OK: > > SqlOperatorBaseTest.java: > .. > tester.checkScalar("{fn TIMESTAMPDIFF(HOUR," > + " TIMESTAMP '2014-03-29 12:34:56'," > + " TIMESTAMP '2014-03-29 12:34:56')}", "0", "INTEGER NOT NULL"); > tester.checkScalar("{fn TIMESTAMPDIFF(MONTH," > + " TIMESTAMP '2019-09-01 00:00:00'," > + " TIMESTAMP '2020-03-01 00:00:00')}", "6", "INTEGER NOT NULL"); > .. > > So I wonder if there is something I'm not considering that is making me > think wrong. Please, any ideas? >