Hm. Is what I am seeing then the effect of Avatica/JDBC ? Another piece of this is that the result differs when using TIMESTAMPDIFF/TIMESTAMPADD, which gives the correct integer value for that time unit. I mention this because the Calcite docs claim that TIMESTAMPDIFF and TIMESTAMPADD are equivalent to `-` and `+`, but they actually behave slightly differently: For example: ` TIMESTAMP'1970-01-01 00:00:00' + (TIMESTAMP'1971-2-10 10:10:10' - TIMESTAMP'1970-01-01 00:00:00') year` Gives 1971-02-01 00:00:00.0 Where as ` TIMESTAMPADD( year, TIMESTAMPDIFF(year, TIMESTAMP'1970-01-01 00:00:00', TIMESTAMP'1971-2-10 10:10:10'), TIMESTAMP'1970-01-01 00:00:00')` Gives 1971-01-01 00:00:00.0
This happens because `-` is giving 13 months, which it then adds to 1970-01 to get 1971-02, but TIMESTAMPDIFF gives 1 (year) which it then adds to 1970-01 to get 1971-01 Thanks! -Ian On 2023/02/07 00:33:48 Julian Hyde wrote: > I ran the queries through SQLLine and got the expected results, as follows. > > 0: jdbc:calcite:model=core/src/test/resources> values ((TIMESTAMP > '1971-01-01 00:00:00' - TIMESTAMP '1970-01-01 00:00:00') second); > +------------------+ > | EXPR$0 | > +------------------+ > | +31536000.000000 | > +------------------+ > 1 row selected (0.582 seconds) > 0: jdbc:calcite:model=core/src/test/resources> values ((TIMESTAMP > '1971-01-01 00:00:00' - TIMESTAMP '1970-01-01 00:00:00') minute); > +---------+ > | EXPR$0 | > +---------+ > | +525600 | > +---------+ > 1 row selected (0.026 seconds) > 0: jdbc:calcite:model=core/src/test/resources> values ((TIMESTAMP > '1971-01-01 00:00:00' - TIMESTAMP '1970-01-01 00:00:00') hour); > +--------+ > | EXPR$0 | > +--------+ > | +8760 | > +--------+ > 1 row selected (0.023 seconds) > > In my queries, the first returns an INTERVAL SECOND, the second returns an > INTERVAL MINUTE, and the third returns an INTERVAL HOUR. All of these > intervals have the same internal representation — 31,536,000,000 milliseconds > — but they are printed differently because their types are different. > > INTERVAL MONTH and INTERVAL YEAR have a different representation, as you > noticed. > > Julian > > > > On Feb 6, 2023, at 3:24 PM, Ian Bertolacci > > <[email protected]<mailto:[email protected]>LID> wrote: > > > > I’ve noticed that Calcite evaluates to different time unit types than the > > given time unit type in a timestamp difference (I haven’t tested datetimes). > > For example: > > `(TIMESTAMP'1971-01-01 00:00:00' - TIMESTAMP'1970-01-01 00:00:00') > > $TIMEUNIT` gives 31536000000 (milliseconds) when $TIMEUNIT = SECOND, > > MINUTE, HOUR, or DAY, but 12 (months) when $TIMEUNIT = MONTH, YEAR > > > > This is inconsistent in two dimensions: > > > > 1. It is not consistent with the time unit specified > > 2. It is not consistent across all time units. (this is the most > > confusing to me) > > > > Is this a bug? > > If not, how are end-users supposed to deal with it? > > Are they just supposed to know that this evaluates to milliseconds for some > > time units but months for others? > > > > Thanks! > > -Ian J. Bertolacci > >
