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]> 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
