Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <881855641.695861270497628034.JavaMail.jira@brutus.apache.org>
Date: Mon, 5 Apr 2010 20:00:28 +0000 (UTC)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-2602) TIMESTAMP value is truncated  when
 return to client
In-Reply-To: <17010421.1178134635553.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-2602?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12853519#action_12853519 ] 

Rick Hillegas commented on DERBY-2602:
--------------------------------------

The Derby timestamp data type is simply a JDBC timestamp, that is, a java.sql.Timestamp. The header comment on that class indicates that it was created for the express purpose of allowing timestamps to have nanosecond precision.

The SQL Standard does not impose any limitation on the precision of timestamps, leaving the issue to the implementer. The relevant sections of the Standard are part 2, section 4.6 (Datetimes and intervals) and part 2, section 5.3 (<literal>).

The embedded driver supports the full range of timestamp values allowed by JDBC. In contrast, the network driver limits timestamps to microsecond precision. This truncation occurs both when timestamps pass from the client to the server and also in the reverse direction. I believe that that is the limit imposed by DB2; see http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.intro/db2z_datetimetimestamp.htm The microsecond limit is also enforced by Derby's builtin TIMESTAMP() function; see the header comment for SQLTimestamp.parseDateOrTimestamp(), which notes that an exception is raised if the argument to TIMESTAMP() "is incorrect for an IBM standard timestamp".

I believe that the network behavior should conform to the existing embedded behavior and that Derby's network layer should support the full range of timestamps allowed by JDBC. There are of course compatibility issues here. I propose the following solution:

o If both the client and the server are Derby code at 10.6 or higher, then timestamps should not be truncated.

o However, if either the client or the server is not Derby code at 10.6 or higher, then truncation should continue to occur.

Implementation notes:

The truncation client->server happens in DateTime.timestampToTimestampBytes().
   which is read by DRDAConnThread.readAndSetParams()

The truncation server->client happens in DRDAConnThread.writeFdocaVal()
  which is read by DateTime.timestampBytesToTimestamp()

> TIMESTAMP value is truncated  when return to client
> ---------------------------------------------------
>
>                 Key: DERBY-2602
>                 URL: https://issues.apache.org/jira/browse/DERBY-2602
>             Project: Derby
>          Issue Type: Bug
>          Components: Network Client
>    Affects Versions: 10.3.1.4
>            Reporter: Kathey Marsden
>            Priority: Minor
>         Attachments: d2602.java
>
>
> In ParameterMappingTest   I see the following differences between embedded and client.  Client is truncating the TIMESTAMP value.  Look for this bug number in the test for reproduction.
>  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; 

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.