We don’t really want TIMESTAMPDIFF to work because it’s not standard (although it is exists in various dialects).
Datetime minus is preferred. It’s more powerful because it accepts an interval value. I haven’t tried that either, but I imagine you would create an interval literal and then pass the result to the ‘-‘ operator. Combining convertlets with RelBuilder is an interesting idea. If you can get that working without adding too much cruft to RelBuilder we should consider it. (I have a very low bar for cruft in RelBuilder because it’s massive - 5,000 lines long - and, as a builder for the core Rel and Rex languages, there should only be one way to do things.) Julian > On Feb 2, 2022, at 3:07 PM, Thomas D'Silva <[email protected]> wrote: > > Hi, > > I get an error while using TIMESTAMPDIFF with RelBuilder. For this example > code > > @Test void testRun2() throws Exception { > // Equivalent SQL: > // SELECT TIMESTAMP(SECOND, HIREDATE, TIMESTAMP'1970-01-01') FROM EMP > final RelBuilder builder = RelBuilder.create(config().build()); > RelNode root = > builder.scan("EMP") > .project( > builder.getRexBuilder().makeCall( > SqlStdOperatorTable.TIMESTAMP_DIFF, > builder.getRexBuilder().makeFlag(TimeUnit.SECOND), > builder.field("HIREDATE"), > > builder.getRexBuilder().makeTimestampLiteral(TimestampString.fromMillisSinceEpoch(0), > 0) > ) > ) > .build(); > > try (PreparedStatement preparedStatement = RelRunners.run(root)) { > String s = CalciteAssert.toString(preparedStatement.executeQuery()); > final String result = ""; > assertThat(s, is(result)); > } > } > > I get an exception > > Suppressed: java.lang.RuntimeException: cannot translate call > TIMESTAMPDIFF($t8, $t4, $t9) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1157) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:98) > > When a sql string is executed it appears that the TIMESTAMPDIFF function is > converted to a difference of two timestamps ( > https://github.com/apache/calcite/blob/a03586c26e1888daebabb271b603fd2871d6a359/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java#L1853 > ). > > Is there a way to use the TIMESTAMPDIFF() with RelBuilder or would I have > to do a similar translation as is done in TimestampDiffConvertlet? > > Thank you, > Thomas
