Derby
  1. Derby
  2. DERBY-163

Timestamps do not display trailing zeros

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0
    • Fix Version/s: 10.2.2.0
    • Component/s: Documentation
    • Environment:
      Windows XP Professional SP1
    • Urgency:
      Normal
    • Issue & fix info:
      Newcomer

      Description

      The timestamp format within Derby contains the following information:

      yyyy-mm-dd-hh.mm.ss.mmmmmm

      When issuing a CURRENT TIMESTAMP function, it returns

      yyyy-mm-dd-hh.mm.ss.mmm

      If you do a TIMESTAMP('1988-12-15-17.12.30.123400') it will return

      1988-12-15-17.12.30.1234

      Is there any particular reason why Derby does not display the zeros at the end of the field? This may just be just to be consistent with the ISO standards, but if you look at the example in the manual, it shows:

      VALUES TIMESTAMP(START_DATE, END_DATE)
      1988-12-25-17.12.30.000000

      If I try this with a simple table:

      CREATE TABLE TS (A DATE, B TIME);
      INSERT INTO TS VALUES (CURRENT DATE, CURRENT TIME);
      SELECT TIMESTAMP(A,B) FROM TS;

      ij> select timestamp(a,b) from ts;
      1
      --------------------------
      2005-03-04 15:13:19.0

      So the 0's are not displayed, except for the first microsecond. The format needs to be clarified either in the manuals or corrected in the program.

        Issue Links

          Activity

          George Baklarz created issue -
          Deepa Remesh made changes -
          Field Original Value New Value
          Component/s Newcomer [ 12310640 ]
          Description The timestamp format within Derby contains the following information:

              yyyy-mm-dd-hh.mm.ss.mmmmmm

          When issuing a CURRENT TIMESTAMP function, it returns

              yyyy-mm-dd-hh.mm.ss.mmm

          If you do a TIMESTAMP('1988-12-15-17.12.30.123400') it will return

              1988-12-15-17.12.30.1234

          Is there any particular reason why Derby does not display the zeros at the end of the field? This may just be just to be consistent with the ISO standards, but if you look at the example in the manual, it shows:

            VALUES TIMESTAMP(START_DATE, END_DATE)
            1988-12-25-17.12.30.000000

          If I try this with a simple table:

          CREATE TABLE TS (A DATE, B TIME);
          INSERT INTO TS VALUES (CURRENT DATE, CURRENT TIME);
          SELECT TIMESTAMP(A,B) FROM TS;

          ij> select timestamp(a,b) from ts;
          1
          --------------------------
          2005-03-04 15:13:19.0

          So the 0's are not displayed, except for the first microsecond. The format needs to be clarified either in the manuals or corrected in the program.
          The timestamp format within Derby contains the following information:

              yyyy-mm-dd-hh.mm.ss.mmmmmm

          When issuing a CURRENT TIMESTAMP function, it returns

              yyyy-mm-dd-hh.mm.ss.mmm

          If you do a TIMESTAMP('1988-12-15-17.12.30.123400') it will return

              1988-12-15-17.12.30.1234

          Is there any particular reason why Derby does not display the zeros at the end of the field? This may just be just to be consistent with the ISO standards, but if you look at the example in the manual, it shows:

            VALUES TIMESTAMP(START_DATE, END_DATE)
            1988-12-25-17.12.30.000000

          If I try this with a simple table:

          CREATE TABLE TS (A DATE, B TIME);
          INSERT INTO TS VALUES (CURRENT DATE, CURRENT TIME);
          SELECT TIMESTAMP(A,B) FROM TS;

          ij> select timestamp(a,b) from ts;
          1
          --------------------------
          2005-03-04 15:13:19.0

          So the 0's are not displayed, except for the first microsecond. The format needs to be clarified either in the manuals or corrected in the program.
          Hide
          Paul J DeCoursey added a comment -

          I think this may just be an error in the docs. A millisecond could at most have 4 digits, but should only have 3, since 1000 milliseconds is 1 second.

          Show
          Paul J DeCoursey added a comment - I think this may just be an error in the docs. A millisecond could at most have 4 digits, but should only have 3, since 1000 milliseconds is 1 second.
          Hide
          Daniel John Debrunner added a comment -

          Derby's TIMESTAMP precision is nano-seconds, so the string format of that should support the complete resolution.

          Show
          Daniel John Debrunner added a comment - Derby's TIMESTAMP precision is nano-seconds, so the string format of that should support the complete resolution.
          Hide
          Kathey Marsden added a comment -

          I noticed in converting the ParameterMappingTest to junit these differences between embedded and client

          case java.sql.Types.TIMESTAMP:
          if (param == 2)
          if (usingEmbedded())
          assertEquals("2004-03-12 21:14:24.938222433", val.toString());
          else
          assertEquals("2004-03-12 21:14:24.938222", val.toString());
          else if (param == 3)
          if (usingEmbedded())
          assertEquals("2004-04-12 04:25:26.462983731", val.toString());
          else
          assertEquals("2004-04-12 04:25:26.462983", val.toString());
          break;

          Is that difference the same as this bug or is it a different issue?

          Show
          Kathey Marsden added a comment - I noticed in converting the ParameterMappingTest to junit these differences between embedded and client case java.sql.Types.TIMESTAMP: if (param == 2) if (usingEmbedded()) assertEquals("2004-03-12 21:14:24.938222433", val.toString()); else assertEquals("2004-03-12 21:14:24.938222", val.toString()); else if (param == 3) if (usingEmbedded()) assertEquals("2004-04-12 04:25:26.462983731", val.toString()); else assertEquals("2004-04-12 04:25:26.462983", val.toString()); break; Is that difference the same as this bug or is it a different issue?
          Hide
          George Baklarz added a comment -

          Not sure if it is the same. I was getting truncation on the digits (which
          does look similar to your code below) but I was getting less digits than
          the documentation suggested I should get. Your code shows 6 or 9
          microseconds, while mine was being truncated at 4 instead of 6. Anyway, I
          haven't followed up on this since it wasn't a current issue for me.

          Thanks.

          George

          Show
          George Baklarz added a comment - Not sure if it is the same. I was getting truncation on the digits (which does look similar to your code below) but I was getting less digits than the documentation suggested I should get. Your code shows 6 or 9 microseconds, while mine was being truncated at 4 instead of 6. Anyway, I haven't followed up on this since it wasn't a current issue for me. Thanks. George
          Hide
          Daniel John Debrunner added a comment -

          I think the problem Kathey is seeing is a separate, more serious, bug.
          The value being returned to the client is not the correct value, but instead a truncated value.
          This bug is for trailing zeros not being displayed.

          Show
          Daniel John Debrunner added a comment - I think the problem Kathey is seeing is a separate, more serious, bug. The value being returned to the client is not the correct value, but instead a truncated value. This bug is for trailing zeros not being displayed.
          Andrew McIntyre made changes -
          Summary Timestamp formatting Timestamps do not display trailing zeros
          Hide
          Ravinder Reddy added a comment -

          Derby's TIMESTAMP precision is nano-seconds.
          But only microsecond resolution is supported in converting strings to timestamps.
          So I think , It should atleast support at microsecond resolution (with trailing zeros)
          Thank You

          Show
          Ravinder Reddy added a comment - Derby's TIMESTAMP precision is nano-seconds. But only microsecond resolution is supported in converting strings to timestamps. So I think , It should atleast support at microsecond resolution (with trailing zeros) Thank You
          Sandeep A made changes -
          Assignee Sandeep A [ dbz_s ]
          Hide
          Kathey Marsden added a comment -

          Haven't heard from the owner in over a year so unassigning. Please reassign yourself if you are still interested in pursuing the issue.

          Show
          Kathey Marsden added a comment - Haven't heard from the owner in over a year so unassigning. Please reassign yourself if you are still interested in pursuing the issue.
          Kathey Marsden made changes -
          Assignee Sandeep A [ dbz_s ]
          Dag H. Wanvik made changes -
          Derby Categories [Newcomer]
          Dag H. Wanvik made changes -
          Component/s Newcomer [ 12310640 ]
          Dag H. Wanvik made changes -
          Issue & fix info [Newcomer]
          Mike Matrigali made changes -
          Urgency Normal
          Kathey Marsden made changes -
          Labels derby_triage10_5_2
          Hide
          Knut Anders Hatlen added a comment -

          Changing component to documentation.

          The example that showed the wrong format was fixed in 10.2.2.0 as part of DERBY-1839. Marking this issue as fixed in that version.

          Show
          Knut Anders Hatlen added a comment - Changing component to documentation. The example that showed the wrong format was fixed in 10.2.2.0 as part of DERBY-1839 . Marking this issue as fixed in that version.
          Knut Anders Hatlen made changes -
          Fix Version/s 10.2.2.0 [ 12312027 ]
          Component/s Documentation [ 11406 ]
          Component/s SQL [ 11408 ]
          Knut Anders Hatlen made changes -
          Link This issue is part of DERBY-1839 [ DERBY-1839 ]
          Knut Anders Hatlen made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Gavin made changes -
          Workflow jira [ 40612 ] Default workflow, editable Closed status [ 12802099 ]
          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open Resolved Resolved
          2855d 5h 35m 1 Knut Anders Hatlen 28/Dec/12 10:54
          Resolved Resolved Closed Closed
          613d 21h 37m 1 Knut Anders Hatlen 03/Sep/14 09:31

            People

            • Assignee:
              Unassigned
              Reporter:
              George Baklarz
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development