Re: Expected conversion for TIMESTAMPDIFF

[Julian Hyde <jh...@apache.org>]

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 <pv...@gmail.com> 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
> 
>