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. mmss_required.diff
        2 kB
        Bryan Pendleton
      2. rrefsqlj27620.html
        7 kB
        Bryan Pendleton
      3. derby234.diff
        8 kB
        Jeff Levitt

        Issue Links

          Activity

          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.
          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.
          Hide
          Jeff Levitt added a comment -

          Fixed in 10.1.0.0

          Show
          Jeff Levitt added a comment - Fixed in 10.1.0.0
          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
          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.
          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?
          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
          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
          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
          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
          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
          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
          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).

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development