Maybe JDBC is involved. Remember SQLLine uses JDBC too. Maybe SQLLine calls getString and you are calling getInt. I don’t know.
Regarding the behavior of TIMESTAMPDIFF when the timestamps cross unit boundaries. There was some discussion of this in one of the Jira cases to add support for BigQuery functions. I can’t find which Jira case. Maybe one of the contributors to those cases can speak up. I’m not sure whether TIMESTAMPDIFF is supposed to be exactly compatible with the ‘-‘ operation defined by the SQL standard. If you have evidence that Calcite’s behavior is correct or incorrect (by reading specifications, or experiments on other DBs) please share it. > On Feb 7, 2023, at 12:03 PM, Ian Bertolacci > <ian.bertola...@workday.com.INVALID> wrote: > > 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 >>> <ia...@workday.com.inva<mailto:ia...@workday.com.inva>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
