db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel John Debrunner (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-889) with client getTimestamp on a TIME column will print the date 1900-01-01 instead of the current date
Date Sun, 03 Sep 2006 00:10:23 GMT
    [ http://issues.apache.org/jira/browse/DERBY-889?page=comments#action_12432311 ] 
Daniel John Debrunner commented on DERBY-889:

Regardless of any Derby implementation details, it's not clear that we have agreement on the
expected behaviour for getTimestamp() on a TIME column.

JDBC spec says it is allowed (conversion table B6)

However this similar conversion are not allowed
    setObject() with a java.sql.Time value into a TIMESTAMP column (table B5)

The ResultSet.getTimestamp javadoc as Bryan pointed out is not much help.

The question is what should be the date portion be for the java.sql.Timestamp value.

First case is the getTimestamp() methods that do *not* pass a Calendar object.

There seem to be four options:

A) 1900-01-01 (which client does)
B) 1970-01-01 
C) the current date at the time the getTimestamp() is called (which embedded tries to do,
though has with bugs in the handling)
D) the value of the SQL function CURRENT_DATE
E) 0000-01-01 (earliest date supported by Derby)

The 1970 date in B) comes from the requirement for the java.sql.Time value to have date components
of 1970-01-01, see javadoc for java.sql.Time.

C) is what Bryan implemented/fixed (I think) - Can you confirm?

D) Would map to the behaviour defined by CAST (<TIME expression> TO TIMESTAMP)

Note that the dates in C) and D) can be different, basically when a query crosses midnight
or takes multiple days to run.

Thinking about it  D) seems to be not an option, this would be impossible for a client driver
to implement unless it added an extra column into every query that had a returned TIME column.
That extra column would be the CURRENT_DATE. Thus the logic for the conversion has to be isolated
to the driver.

Option A) seems invalid to me, why 1900-01-01, that's not the start of the Derby DATE range
or the java.sql.Timestamp DATE range, why is that date better than any other?

Option B) has some merit it's the epoch date for Java time values, doesn't seem that useful

Note that the same issue exists for DATE to TIMESTAMP, comments in the embedded code indicate
the intended behaviour is to generate
a TIMSTAMP with a time portion of 00:00:00 (midnight). That would correspond to E) in the
TIME to TIMESTAMP conversion.

So the intended embedded behaviour is

TIME->java.sql.Timestamp - date component set to current date
DATE -> java.sql.Timestamp - time component set to 00:00:00 (midnight)

These both seem reasonable, even though they are not symmetric.

I would then assert for the getTimestamp() methods with a passed in Calendar, that the behaviour
is exactly the same. The only difference is that the long milliseconds value (since 1970)
 in the java.sql.Timestamp
is different to account for the time zone of the passed in Calendar, which I think matches
the javadoc for these methods.

Seems good?

> with client getTimestamp on a TIME column will print the date  1900-01-01 instead of
the current date
> -----------------------------------------------------------------------------------------------------
>                 Key: DERBY-889
>                 URL: http://issues.apache.org/jira/browse/DERBY-889
>             Project: Derby
>          Issue Type: Bug
>          Components: Network Client
>    Affects Versions:,,,
>            Reporter: Kathey Marsden
>         Assigned To: Bryan Pendleton
>         Attachments: derby-889-updated-Aug-2006.diff, derby-889.diff
> On client getTimestamp on a TIME column will print date   1900-01-01 instead of the current
date like the embedded driver.
> To repro run the DERBY-877 repro without specifying a file.encoding
> java TestEnc derbynetclient
> [snip]
>         getString:      16:27:35
>         getTimeStamp:   1900-01-01 16:27:35.0
>         getTime:        16:27:35
>         getDate         Exception SQLSTATE:null  (EXPECTED)
> With Embedded  it prints the current date for getTimestamp
> java TestEnc  derby
>         getString:      16:27:35
>         getTimeStamp:   2006-01-28 16:27:35.0
>         getTime:        16:27:35
>         getDate         Exception SQLSTATE:22005  (EXPECTED)

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message