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* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> 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
|
- Re: fn timestampdiff gives error... Lance J. Andersen
-
