Re: Why does timestamp diff return a value inconsistent with the time unit?

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

RE: Re: Why does timestamp diff return a value inconsistent with the time unit?

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

Re: Why does timestamp diff return a value inconsistent with the time unit?

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

Why does timestamp diff return a value inconsistent with the time unit?

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 3153600 (milliseconds) when $TIMEUNIT =