db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lance J. Andersen" <Lance.Ander...@Sun.COM>
Subject Re: fn timestampdiff gives error...
Date Mon, 28 Nov 2005 23:26:21 GMT


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.
>  
>
JDBC/ODBC define that the return type range for the TIMESTAMPDIFF 
function is an INTEGER regardless of what the backend engine supports.

>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