Thanks all, for your input, I'll go tweak those values.
Myrna
On 11/28/05, Satheesh Bandaram <satheesh@sourcery.org> wrote:
>
> Yes, Timestampdiff is coded to return an INT, which has the following
> range. May be it could return a BIGINT if argument is of type
> SQL_TSI_FRAC_SECOND. INT
> 2147483647 (ava.lang.Integer.MAX_VALUE)
> 2147483648 (java.lang.Integer.MIN_VALUE)
>
> So, the query is exceeding the range.
>
> Satheesh
>
> Daniel John Debrunner wrote:
>
> Myrna van Lunteren wrote:
>
>
> Well, I can see that smaller differences are tested (test
> lang/timestampArith.java) but these values were pulled out of Sun's
> (jdbc) compliance test suite, and I thought one shouldn't modify those
> values? Lance, you may know more about that detail...
>
> Should timestampdiff be able to handle the range of the two timestamps
> in this particular query?
>
>
> So one year is roughly 3.1e7 seconds, which is 3.1e16 fractions of a
> second. ODBC defines SQL_TSI_FRAC_SECOND as " fractional seconds are
> expressed in billionths of a second". I assume this is US billion, 1e9.
>
> So 2 years (roughly the example you have) should be 6.2e16, outside the
> range for an INTEGER but within the range for a BIGINT.
>
> Is this function always returning an INTEGER?
>
> My guess is that it's up to the engine to define the range it handles,
> not ODBC or JDBC.
>
> Dan.
>
>
>
> Myrna
>
> On 11/28/05, *Lance J. Andersen* <Lance.Andersen@sun.com<mailto:Lance.Andersen@sun.com>
<Lance.Andersen@sun.com>> wrote:
>
> Myrna,
>
> Try having the timestamp values closer together and give it a go.
>
> lance
>
>
> Myrna van Lunteren wrote:
>
>
>
> Hi,
>
> Is this a bug? It seems to me it is...
>
> In ij:
> ij> create table tab1(TMSTCOL1 TIMESTAMP, TMSTCOL2 TIMESTAMP);
> 0 rows inserted/updated/deleted
> ij> INSERT INTO tab1 VALUES ({ts '19940612 11:20:10'},{ts
> '19960510 10:07:05'});
> 1 row inserted/updated/deleted
> ij> SELECT {FN
> TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,TMSTCOL1,TMSTCOL2)} FROM tab1;
> 1
> 
> ERROR 22003: The resulting value is outside the range for the data
> type INTEGER.
>
> Thx,
> Myrna
>
>
>
>
>
