db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Myrna van Lunteren <m.v.lunte...@gmail.com>
Subject Re: fn timestampdiff gives error...
Date Mon, 28 Nov 2005 23:39:18 GMT
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, 1e-9.
>
> 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 '1994-06-12 11:20:10'},{ts
>     '1996-05-10 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
>
>
>
>
>

Mime
View raw message