db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: fn timestampdiff gives error...
Date Mon, 28 Nov 2005 23:31:35 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
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.
<table
 style="border: 1pt solid black; margin-top: 12pt; margin-bottom: 10pt; background-color:
white;"
 valign="top">
  <tbody>
    <tr>
      <td
 style="border: 1pt solid black; padding: 2pt; margin-left: 2pt; background-color: rgb(250,
244, 250);"
 valign="top">
      <div>INT</div>
      </td>
      <td
 style="border: 1pt solid black; padding: 2pt; margin-left: 2pt; background-color: rgb(250,
244, 250);"
 valign="top">
      <div>2147483647 (<span style="font-style: italic;">ava.lang.Integer.MAX_VALUE</span>)</div>
      </td>
      <td
 style="border: 1pt solid black; padding: 2pt; margin-left: 2pt; background-color: rgb(250,
244, 250);"
 valign="top">
      <div>-2147483648 (<span style="font-style: italic;">java.lang.Integer.MIN_VALUE</span>)</div>
      </td>
    </tr>
  </tbody>
</table>
<br>
So, the query is exceeding the range.<br>
<br>
Satheesh<br>
<br>
Daniel John Debrunner wrote:
<blockquote cite="mid438B8EBC.3060606@debrunners.com" type="cite">
  <pre wrap="">Myrna van Lunteren wrote:
  </pre>
  <blockquote type="cite">
    <pre wrap="">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?
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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.

  </pre>
  <blockquote type="cite">
    <pre wrap="">Myrna
 
On 11/28/05, *Lance J. Andersen* &lt;<a class="moz-txt-link-abbreviated" href="mailto:Lance.Andersen@sun.com">Lance.Andersen@sun.com</a>
<a class="moz-txt-link-rfc2396E" href="mailto:Lance.Andersen@sun.com">&lt;mailto:Lance.Andersen@sun.com&gt;</a>&gt;
wrote:

    Myrna,

    Try having the timestamp values closer together and give it a go.

    -lance


    Myrna van Lunteren wrote:

    </pre>
    <blockquote type="cite">
      <pre wrap="">    Hi,
     
    Is this a bug? It seems to me it is...
     
    In ij:
    ij&gt; create table tab1(TMSTCOL1 TIMESTAMP, TMSTCOL2 TIMESTAMP);
    0 rows inserted/updated/deleted
    ij&gt; INSERT INTO tab1 VALUES ({ts '1994-06-12 11:20:10'},{ts
    '1996-05-10 10:07:05'});
    1 row inserted/updated/deleted
    ij&gt; 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
      </pre>
    </blockquote>
    <pre wrap="">
    </pre>
  </blockquote>
  <pre wrap=""><!---->



  </pre>
</blockquote>
</body>
</html>


Mime
View raw message