Derby
  1. Derby
  2. DERBY-4626

Timestamp truncated when converted to string with explicit calendar

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.5.3.0
    • Fix Version/s: 10.5.3.2, 10.6.2.4, 10.7.1.1
    • Component/s: SQL
    • Labels:
      None
    • Bug behavior facts:
      Wrong query result

      Description

      When setting the value of a VARCHAR parameter with setTimestamp() the timestamp is truncated to microsecond resolution if a calendar is specified. Nanosecond resolution is preserved when a calendar is not specified. These two methods should behave the same way. Since Derby supports nanosecond resolution, the timestamps should not be truncated.

      1. TimestampToVarchar.java
        1 kB
        Knut Anders Hatlen
      2. test.diff
        2 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          The attached class, TimestampToVarchar, shows the difference between the two methods. Note these two differences:

          1) The setTimestamp() method that takes a Calendar truncates nanoseconds so that the resulting resolution is microseconds. The method that doesn't take a Calendar preserves the nanoseconds.

          2) The setTimestamp() method that takes a Calendar does not remove trailing zeros, whereas the method that doesn't take a Calendar does remove trailing zeros (except if the fraction is zero).

          Here's the output of the repro on trunk:

          Testing timestamp: 2010-04-20 15:17:36.123456789

          • without calendar: 2010-04-20 15:17:36.123456789
          • with calendar: 2010-04-20 15:17:36.123457

          Testing timestamp: 2010-04-20 15:17:36.12345678

          • without calendar: 2010-04-20 15:17:36.12345678
          • with calendar: 2010-04-20 15:17:36.123457

          Testing timestamp: 2010-04-20 15:17:36.1234567

          • without calendar: 2010-04-20 15:17:36.1234567
          • with calendar: 2010-04-20 15:17:36.123457

          Testing timestamp: 2010-04-20 15:17:36.123456

          • without calendar: 2010-04-20 15:17:36.123456
          • with calendar: 2010-04-20 15:17:36.123456

          Testing timestamp: 2010-04-20 15:17:36.12345

          • without calendar: 2010-04-20 15:17:36.12345
          • with calendar: 2010-04-20 15:17:36.123450

          Testing timestamp: 2010-04-20 15:17:36.1234

          • without calendar: 2010-04-20 15:17:36.1234
          • with calendar: 2010-04-20 15:17:36.123400

          Testing timestamp: 2010-04-20 15:17:36.123

          • without calendar: 2010-04-20 15:17:36.123
          • with calendar: 2010-04-20 15:17:36.123000

          Testing timestamp: 2010-04-20 15:17:36.12

          • without calendar: 2010-04-20 15:17:36.12
          • with calendar: 2010-04-20 15:17:36.120000

          Testing timestamp: 2010-04-20 15:17:36.1

          • without calendar: 2010-04-20 15:17:36.1
          • with calendar: 2010-04-20 15:17:36.100000

          Testing timestamp: 2010-04-20 15:17:36.0

          • without calendar: 2010-04-20 15:17:36.0
          • with calendar: 2010-04-20 15:17:36.0
          Show
          Knut Anders Hatlen added a comment - The attached class, TimestampToVarchar, shows the difference between the two methods. Note these two differences: 1) The setTimestamp() method that takes a Calendar truncates nanoseconds so that the resulting resolution is microseconds. The method that doesn't take a Calendar preserves the nanoseconds. 2) The setTimestamp() method that takes a Calendar does not remove trailing zeros, whereas the method that doesn't take a Calendar does remove trailing zeros (except if the fraction is zero). Here's the output of the repro on trunk: Testing timestamp: 2010-04-20 15:17:36.123456789 without calendar: 2010-04-20 15:17:36.123456789 with calendar: 2010-04-20 15:17:36.123457 Testing timestamp: 2010-04-20 15:17:36.12345678 without calendar: 2010-04-20 15:17:36.12345678 with calendar: 2010-04-20 15:17:36.123457 Testing timestamp: 2010-04-20 15:17:36.1234567 without calendar: 2010-04-20 15:17:36.1234567 with calendar: 2010-04-20 15:17:36.123457 Testing timestamp: 2010-04-20 15:17:36.123456 without calendar: 2010-04-20 15:17:36.123456 with calendar: 2010-04-20 15:17:36.123456 Testing timestamp: 2010-04-20 15:17:36.12345 without calendar: 2010-04-20 15:17:36.12345 with calendar: 2010-04-20 15:17:36.123450 Testing timestamp: 2010-04-20 15:17:36.1234 without calendar: 2010-04-20 15:17:36.1234 with calendar: 2010-04-20 15:17:36.123400 Testing timestamp: 2010-04-20 15:17:36.123 without calendar: 2010-04-20 15:17:36.123 with calendar: 2010-04-20 15:17:36.123000 Testing timestamp: 2010-04-20 15:17:36.12 without calendar: 2010-04-20 15:17:36.12 with calendar: 2010-04-20 15:17:36.120000 Testing timestamp: 2010-04-20 15:17:36.1 without calendar: 2010-04-20 15:17:36.1 with calendar: 2010-04-20 15:17:36.100000 Testing timestamp: 2010-04-20 15:17:36.0 without calendar: 2010-04-20 15:17:36.0 with calendar: 2010-04-20 15:17:36.0
          Hide
          Knut Anders Hatlen added a comment -

          The fix for DERBY-4625 also fixed this issue. I've added a regression test case in DateTimeTest to verify the fix. Committed revision 999485.

          Show
          Knut Anders Hatlen added a comment - The fix for DERBY-4625 also fixed this issue. I've added a regression test case in DateTimeTest to verify the fix. Committed revision 999485.
          Hide
          Kathey Marsden added a comment -

          Reopen for backport

          Show
          Kathey Marsden added a comment - Reopen for backport
          Hide
          Mike Matrigali added a comment -

          temp taking ownership to backport to 10.6 and 10.5

          Show
          Mike Matrigali added a comment - temp taking ownership to backport to 10.6 and 10.5
          Hide
          Mike Matrigali added a comment -

          backported fix from trunk to 10.6 and 10.5. resetting original owner and resolving.

          Show
          Mike Matrigali added a comment - backported fix from trunk to 10.6 and 10.5. resetting original owner and resolving.

            People

            • Assignee:
              Nirmal Fernando
              Reporter:
              Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development