Derby
  1. Derby
  2. DERBY-234

Documentation of DateTime types is incomplete

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0
    • Fix Version/s: 10.3.1.4
    • Component/s: Documentation
    • Labels:
      None

      Description

      The documentation for datatypes DATE, TIME, and TIMESTAMP is incomplete. The documentation says that DATE, TIME, and TIMESTAMP accept any values accepted by the java.sql.Date, java.sql.Time, and java.sql.Timestamp classes respectively. Derby accepts a number of string formats:

      DATE:
      yyyy-mm-dd
      mm/dd/yyyy
      dd.mm.yyyy

      TIME:
      hh:mm[:ss]
      hh.mm[.ss]
      hh[:mm]

      {AM | PM}

      TIMESTAMP:
      yyyy-mm-dd-hh[.mm[.ss[.nnnnnn]]]
      yyyy-mm-dd hh[:mm[:ss[.nnnnnn]]]

      The year must always have 4 digits. Months, days, and hours may have one or two digits. Minutes and seconds, if present, must have two digits. Nanoseconds, if present may have 1 to 6 digits.

      Derby also accepts strings in the locale specific datetime format, using the locale of the database server. If there is an ambiguity the built in formats above take precedence.

      1. derby234.diff
        8 kB
        Jeff Levitt
      2. mmss_required.diff
        2 kB
        Bryan Pendleton
      3. rrefsqlj27620.html
        7 kB
        Bryan Pendleton

        Issue Links

          Activity

          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open Resolved Resolved
          12d 11h 50m 1 Jean T. Anderson 05/May/05 12:41
          Closed Closed Reopened Reopened
          530d 17h 42m 1 Bryan Pendleton 20/Nov/06 03:24
          Reopened Reopened Resolved Resolved
          46d 28m 1 Bryan Pendleton 05/Jan/07 03:52
          Resolved Resolved Closed Closed
          417d 6h 18m 2 Dyre Tjeldvoll 24/Jan/08 13:09
          Gavin made changes -
          Workflow jira [ 41936 ] Default workflow, editable Closed status [ 12797577 ]
          Dyre Tjeldvoll made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Hide
          Dyre Tjeldvoll added a comment -

          This issue is resolved and has not been updated in the last 12 months (since 24/Jan/07).

          Show
          Dyre Tjeldvoll added a comment - This issue is resolved and has not been updated in the last 12 months (since 24/Jan/07).
          Bryan Pendleton made changes -
          Derby Info [Patch Available]
          Resolution Fixed [ 1 ]
          Status Reopened [ 4 ] Resolved [ 5 ]
          Fix Version/s 10.3.0.0 [ 12310800 ]
          Hide
          Bryan Pendleton added a comment -

          Committed mmss_required.diff to the docs trunk as subversion revision 492893.

          Show
          Bryan Pendleton added a comment - Committed mmss_required.diff to the docs trunk as subversion revision 492893.
          Hide
          Bryan Pendleton added a comment -

          Many thanks for the good discussion! After digesting these comments for a few days, I've come to the conclusion that I should commit the current mmss_required.diff. I believe it makes the doc more accurately describe the behavior of the code, which is reason enough to commit it as is, I think.

          Show
          Bryan Pendleton added a comment - Many thanks for the good discussion! After digesting these comments for a few days, I've come to the conclusion that I should commit the current mmss_required.diff. I believe it makes the doc more accurately describe the behavior of the code, which is reason enough to commit it as is, I think.
          Hide
          Daniel John Debrunner added a comment -

          Dag wrote:

          > I would prefer the documentation to clearly mark the standard syntax.
          > Extensions ease porting apps to Derby, so documenting it is OK
          > in my view, as longs as the docs make it abundantly clear what is the preferred (standard) syntax.

          I agree it would be an interesting idea to mark which syntax/formats are described by a standard but "preferred" is subjective.
          Documentation should point out factual advantages & disadvantages of any approach but not recommend one, that's up to the application developer to decide.

          Show
          Daniel John Debrunner added a comment - Dag wrote: > I would prefer the documentation to clearly mark the standard syntax. > Extensions ease porting apps to Derby, so documenting it is OK > in my view, as longs as the docs make it abundantly clear what is the preferred (standard) syntax. I agree it would be an interesting idea to mark which syntax/formats are described by a standard but "preferred" is subjective. Documentation should point out factual advantages & disadvantages of any approach but not recommend one, that's up to the application developer to decide.
          Hide
          Dag H. Wanvik added a comment -

          Hi Bryan, Bernt

          I don't know the background for the undelimited timestamp literal
          syntax. I found it is only accepted as input to the TIMESTAMP
          function, not as a stand-alone timestamp literal:

          ij> insert into t4 values ('20070101205520');
          ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
          ij> insert into t4 values timestamp('20070101205520');
          1 row inserted/updated/deleted

          This is actually documented here:
          http://db.apache.org/derby/docs/10.2/ref/rreftimestampfunc.html

          Some of the Derby timestamp syntax extensions (relative to SQL/JDBC), for
          example period notation in hh.mm.ss, is there for IBM DB2 compatibility (called
          out in the code).

          Allowing omission of the leading zero of month, day and hour is also
          an extension, as far as I can see.

          I would prefer the documentation to clearly mark the standard
          syntax. Extensions ease porting apps to Derby, so documenting it is OK
          in my view, as longs as the docs make it abundantly clear what is the
          preferred (standard) syntax.

          I am not sure what the final caveat really means for timestamps
          ("accepts strings in the locale specific datetime format"), does it
          apply? It is relevant for date, obviously..

          Show
          Dag H. Wanvik added a comment - Hi Bryan, Bernt I don't know the background for the undelimited timestamp literal syntax. I found it is only accepted as input to the TIMESTAMP function, not as a stand-alone timestamp literal: ij> insert into t4 values ('20070101205520'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into t4 values timestamp('20070101205520'); 1 row inserted/updated/deleted This is actually documented here: http://db.apache.org/derby/docs/10.2/ref/rreftimestampfunc.html Some of the Derby timestamp syntax extensions (relative to SQL/JDBC), for example period notation in hh.mm.ss, is there for IBM DB2 compatibility (called out in the code). Allowing omission of the leading zero of month, day and hour is also an extension, as far as I can see. I would prefer the documentation to clearly mark the standard syntax. Extensions ease porting apps to Derby, so documenting it is OK in my view, as longs as the docs make it abundantly clear what is the preferred (standard) syntax. I am not sure what the final caveat really means for timestamps ("accepts strings in the locale specific datetime format"), does it apply? It is relevant for date, obviously..
          Hide
          Bernt M. Johnsen added a comment -

          The SQL standard specifies (Extract from SQL 2003 Ch 5.3):

          <date value> ::= <years value> <minus sign> <months value> <minus sign> <days value>
          <time value> ::= <hours value> <colon> <minutes value> <colon> <seconds value>

          <unquoted date string> ::= <date value>
          <unquoted time string> ::= <time value> [ <time zone interval> ]
          <unquoted timestamp string> ::= <unquoted date string> <space> <unquoted time string>

          So there's a lot of Derby "extensions":

          DATE:
          yyyy-mm-dd ok.
          mm/dd/yyyy "extension"
          dd.mm.yyyy "extension"

          TIME:
          hh:mm[:ss] "extension", SQL specifies hh:mm:ss
          hh.mm[.ss] "extension"
          hh[:mm]

          {AM | PM}

          "extension"

          TIMESTAMP:
          yyyy-mm-dd-hh[.mm[.ss[.nnnnnn]]] "extension"
          yyyy-mm-dd hh[:mm[:ss[.nnnnnn]]] "extension", SQL specifies yyyy-mm-dd hh:mm:ss[.nnnnnnn]

          And the variant Dag obeserved:
          yyyymmddhhmmss is also an "extension"

          I think we should document the extensions (including the last one), but also document that they are NOT standards compliant

          Note: SQL deviates from ISO 8601, since <space> is used between date and time instead of "T".

          Show
          Bernt M. Johnsen added a comment - The SQL standard specifies (Extract from SQL 2003 Ch 5.3): <date value> ::= <years value> <minus sign> <months value> <minus sign> <days value> <time value> ::= <hours value> <colon> <minutes value> <colon> <seconds value> <unquoted date string> ::= <date value> <unquoted time string> ::= <time value> [ <time zone interval> ] <unquoted timestamp string> ::= <unquoted date string> <space> <unquoted time string> So there's a lot of Derby "extensions": DATE: yyyy-mm-dd ok. mm/dd/yyyy "extension" dd.mm.yyyy "extension" TIME: hh:mm [:ss] "extension", SQL specifies hh:mm:ss hh.mm [.ss] "extension" hh [:mm] {AM | PM} "extension" TIMESTAMP: yyyy-mm-dd-hh[.mm[.ss [.nnnnnn] ]] "extension" yyyy-mm-dd hh[:mm[:ss [.nnnnnn] ]] "extension", SQL specifies yyyy-mm-dd hh:mm:ss [.nnnnnnn] And the variant Dag obeserved: yyyymmddhhmmss is also an "extension" I think we should document the extensions (including the last one), but also document that they are NOT standards compliant Note: SQL deviates from ISO 8601, since <space> is used between date and time instead of "T".
          Hide
          Bryan Pendleton added a comment -

          Hi Dag, thanks for pointing this out.

          Is that a standard format? Or is that a Derby extension? If it
          is a Derby extension, I'd be tempted not to document it, since
          I think we would prefer users to use the standard formats, right?

          Show
          Bryan Pendleton added a comment - Hi Dag, thanks for pointing this out. Is that a standard format? Or is that a Derby extension? If it is a Derby extension, I'd be tempted not to document it, since I think we would prefer users to use the standard formats, right?
          Hide
          Dag H. Wanvik added a comment -

          In addition to the formats noted above, Derby also accepts
          a plain 14-char length string literal for timestamp:

          ij> values (TIMESTAMP('20070102183500'));
          1
          --------------------------
          2007-01-02 18:35:00.0

          1 row selected

          (See code in SQLTimestamp#computeTimestampFunction).
          Should this be documented, too?

          Show
          Dag H. Wanvik added a comment - In addition to the formats noted above, Derby also accepts a plain 14-char length string literal for timestamp: ij> values (TIMESTAMP('20070102183500')); 1 -------------------------- 2007-01-02 18:35:00.0 1 row selected (See code in SQLTimestamp#computeTimestampFunction). Should this be documented, too?
          Bryan Pendleton made changes -
          Derby Info [Patch Available]
          Bryan Pendleton made changes -
          Attachment mmss_required.diff [ 12348113 ]
          Attachment rrefsqlj27620.html [ 12348114 ]
          Hide
          Bryan Pendleton added a comment -

          Attached is mmss_required.diff, a tiny change to the docs to match the
          current behavior of the code; namely, that MM and SS values are
          required for TIMESTAMP and are not optional. Only nanos are optional.

          Also attached is rrefsqlj27620.html, the HTML-formatted result of the change,
          for easier review.

          Show
          Bryan Pendleton added a comment - Attached is mmss_required.diff, a tiny change to the docs to match the current behavior of the code; namely, that MM and SS values are required for TIMESTAMP and are not optional. Only nanos are optional. Also attached is rrefsqlj27620.html, the HTML-formatted result of the change, for easier review.
          Bryan Pendleton made changes -
          Assignee Bryan Pendleton [ bryanpendleton ]
          Andrew McIntyre made changes -
          Link This issue relates to DERBY-2203 [ DERBY-2203 ]
          Bryan Pendleton made changes -
          Resolution Fixed [ 1 ]
          Status Closed [ 6 ] Reopened [ 4 ]
          Hide
          Bryan Pendleton added a comment -

          I think the timestamp doc that was changed by this issue is not quite correct.

          I think that the minutes and seconds portion of the timestamp string format are not optional;
          only the nanoseconds portion can be omitted. I think this page needs to be updated to
          indicate that minutes and seconds must be provided:
          http://db.apache.org/derby/docs/dev/ref/rrefsqlj27620.html

          See this discussion on the derby-user list for some more discussion on the subject.
          http://www.nabble.com/Date---Timestamp-format-for-inserts--tf2665089.html

          Show
          Bryan Pendleton added a comment - I think the timestamp doc that was changed by this issue is not quite correct. I think that the minutes and seconds portion of the timestamp string format are not optional; only the nanoseconds portion can be omitted. I think this page needs to be updated to indicate that minutes and seconds must be provided: http://db.apache.org/derby/docs/dev/ref/rrefsqlj27620.html See this discussion on the derby-user list for some more discussion on the subject. http://www.nabble.com/Date---Timestamp-format-for-inserts--tf2665089.html
          Jeff Levitt made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Hide
          Jeff Levitt added a comment -

          Fixed in 10.1.0.0

          Show
          Jeff Levitt added a comment - Fixed in 10.1.0.0
          Jean T. Anderson made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Hide
          Jean T. Anderson added a comment -

          Patch applied and committed revision 168252.

          Show
          Jean T. Anderson added a comment - Patch applied and committed revision 168252.
          Jeff Levitt made changes -
          Field Original Value New Value
          Attachment derby234.diff [ 19847 ]
          Hide
          Jeff Levitt added a comment -

          This patch adds the missing documentation to the three datatypes. The information and examples provided by Jack in this issue have been placed in the DATE, TIME, and TIMESTAMP files accordingly.

          Show
          Jeff Levitt added a comment - This patch adds the missing documentation to the three datatypes. The information and examples provided by Jack in this issue have been placed in the DATE, TIME, and TIMESTAMP files accordingly.
          Jack Klebanoff created issue -

            People

            • Assignee:
              Bryan Pendleton
              Reporter:
              Jack Klebanoff
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development