Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-2386

timestampdiff function fails when using SQL_TSI_FRAC_SECOND for datepart parameter, except for very small intervals

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.2.0
    • Fix Version/s: 10.3.1.4
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      SUSE Linux Enterprise Desktop 10
    • Urgency:
      Normal
    • Issue & fix info:
      Release Note Needed

      Description

      Using the timestampdiff function produces and integer overflow except for very small intervals. Error message is:

      [Error Code: -1, SQL State: 22003] The resulting value is outside the range for the data type INTEGER.

      I inserted the following row into my test table:

      insert into datetest (ID,
      startdate,
      enddate) values (
      5,
      '2006-11-20 04:20:00.0',
      '2006-11-20 04:20:00.2');

      This test row works:

      select

      {fn timestampdiff(SQL_TSI_FRAC_SECOND, startdate, enddate)}

      as diff from datetest where id = 5

      DIFF
      200000000

      The value also looks too large, which could be exacerbating the problem.

        Attachments

        1. derby2386.diff
          2 kB
          Mayuresh Nirhali
        2. derby2386-v2.diff
          2 kB
          Mayuresh Nirhali
        3. releaseNote.html
          4 kB
          Myrna van Lunteren
        4. releaseNote.html
          4 kB
          Myrna van Lunteren
        5. releaseNote.html
          4 kB
          Myrna van Lunteren

          Activity

            People

            • Assignee:
              mayureshnirhali Mayuresh Nirhali
              Reporter:
              dhsmith Don Smith
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: