db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: fn timestampdiff gives error...
Date Mon, 28 Nov 2005 23:11:56 GMT
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>> 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