Why the division by 12? The SQL in that bug contains “YEAR”:
{fn TIMESTAMPDIFF(SQL_TSI_YEAR,TIMESTAMP '2016-01-01 00:00:00', TIMESTAMP
'2017-01-01 00:00:00’)}
So I presume that we are computing the difference in months, then dividing by
12 to get years. (There are two fundamental kinds of interval in SQL: seconds
(including fractions of a second) and months; the other interval types are
computed by dividing those by a constant factor.)
Calcite’s return type is an interval. But internally we represent intervals as
numbers, which is why the CAST expression you cite seems to be working in terms
of integers. So, don’t worry, it will come out as an interval.
Julian
> On Aug 30, 2019, at 2:57 AM, Pavel Gubin <[email protected]> wrote:
>
> Trying to understand expected conversion to SQL for TIMESTAMPDIFF function.
> According to this bug:
> https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-3312
> <https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-3312> it is
> converted to something like:
> CAST(/INT(Reinterpret(-(2017-01-01 00:00:00, 2016-01-01 00:00:00)),
> 12)):INTEGER NOT NULL
> which seems incorrect and missing some info for many databases.
>
> For example, PostgreSQL returns interval in days here:
>
> select TIMESTAMP '2017-01-01 00:00:00' - TIMESTAMP '2016-01-01 00:00:00';
> ?column?
> ----------
> 366 days
> (1 row)
>
> How Reinterpret supposed to be converted for PostgreSQL given that it doesn't
> contain output type and what's with division by 12?
>
> Willing to fix this, could somebody give an insight?
>
> Thanks,
> Pavel
>
>
