Re: Question about timestampdiff
Is your question about semantics or implementation? If it's semantics, it's useful if you provide an example where Calcite gives a different result than another DBMS. I find https://rextester.com/l/mysql_online_compiler useful for that. If it's implementation, you should know that YEAR and MONTH have very different semantics and code paths to time units DAY and smaller. Calcite uses interval arithmetic, e.g. (DATE '1971-01-01' - DATE '1970-01-01') MONTH whose behavior is specified in the SQL standard. For us, TIMESTAMPDIFF is just syntactic sugar for that. Julian On Sun, Jun 21, 2020 at 7:25 PM Rui Wang wrote: > > 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 > 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)), 8640)):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)), 259200)):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? > >
Re: Question about timestampdiff
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 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)), 8640)):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)), 259200)):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? >
Question about timestampdiff
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)), 8640)):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)), 259200)):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?