Re: Date different

On Thu, 13 Oct 2005 12:31:51 +0800, Roedy Green wrote
(in article <ppork1d62ld9se84t1vu0rt8ud5tievh9j@xxxxxxx>):

> On Wed, 12 Oct 2005 17:05:24 +0800, steve <steve@xxxxxxx> wrote or
> quoted :
>
>> i tracked it down!!
>> oracle changed the default types returned for their newest oracle drivers,
>> so
>> getobject() actually returns the WRONG type.
>
> I thought JDBC was supposed to bypass that nonsense.
>

well jdbc only return what the database tells it.

This is what oracle have to say on the matter.


What is going on with DATE and TIMESTAMP?
This section is on simple data types. :-)
Prior to 9.2, the Oracle JDBC drivers mapped the DATE SQL type to
java.sql.Timestamp. This made a certain amount of sense because the Oracle
DATE SQL type contains both date and time information as does
java.sql.Timestamp. The more obvious mapping to java.sql.Date was somewhat
problematic as java.sql.Date does not include time information. It was also
the case that the RDBMS did not support the TIMESTAMP SQL type, so there was
no problem with mapping DATE to Timestamp.
In 9.2 TIMESTAMP support was added to the RDBMS. The difference between DATE
and TIMESTAMP is that TIMESTAMP includes nanoseconds and DATE does not. So,
beginning in 9.2, DATE is mapped to Date and TIMESTAMP is mapped to
Timestamp. Unfortunately if you were relying on DATE values to contain time
information, there is a problem.
There are several ways to address this problem:
? Alter your tables to use TIMESTAMP instead of DATE. This is probably
rarely possible, but it is the best solution when it is.
? Alter your application to use defineColumnType to define the columns as
TIMESTAMP rather than DATE. There are problems with this because you really
don't want to use defineColumnType unless you have to (see What is
defineColumnType and when should I use it?).
? Alter you application to use getTimestamp rather than getObject. This i
s a good solution when possible, however many applications contain generic
code that relies on getObject, so it isn't always possible.
? Set the V8Compatibility connection property. This tells the JDBC driver
s to use the old mapping rather than the new one. You can set this flag
either as a connection property or a system property. You set the connection
property by adding it to the java.util.Properties object passed to
DriverManager.getConnection or to OracleDataSource.setConnectionProperties.
You set the system property by including a -D option in your java command
line.
? java -Doracle.jdbc.V8Compatibility="true" MyApp

which basically means that if you coded for date returning timestanp it
worked.
the "alter your application" solution is just stupid, as you now have to
produce either table specific java code or check the metadata and filter
out the dates.

changing you table to timestamp , means exporting the data , and scrapping
off the old table , rebuilding it , and then re-importing the data.


their solution of mapping date to date , is still wrong, because date still
returns time information , which is lost in the translation to date.

to make it more complex, on the return trip back to the database, if you use
stored sql procedures to write the data back to the database, they are also
now broken.
because they are expecting a "Date", but now oracle has returned a
"Timestamp"


.

Relevant Pages

  • Re: problems inserting a utf8 timestamp with DBD::Oracle
    ... Don't think so as I'm not using Oracle 9. ... Well I'm not saying it isn't a bug in Oracle but this one is a real problem I cannot easily workaround as really I need a timestamp and a date. ... Client is Oracle 10.2.0 XE ... If you then comment out the 2nd block of code and uncomment the 3rd block of code it works i.e it appears it does not like having a timestamp and a date in the same insert. ...
    (perl.dbi.users)
  • Re: suggestions sought on returning rows from oracle proc and deleting them in same proc
    ... this is not Perl stuff but Oracle PL/SQL. ... deleting them in same proc ... create mytable (id int, created timestamp); ... Perl calls the first one to get the unique ids and the timestamp ...
    (perl.dbi.users)
  • Re: timezone - dst calendar questions
    ... Oracle Enterprise Manager agent were broken. ... HPUX box then compared the timestamp shown by 'ls -l' ... Perhaps the system time zone is wrongly set. ...
    (comp.unix.admin)
  • Re: timezone - dst calendar questions
    ... Oracle Enterprise Manager agent were broken. ... between my desktop and my db server. ... HPUX box then compared the timestamp shown by 'ls -l' ... Perhaps the system time zone is wrongly set. ...
    (comp.unix.admin)
  • PHP and mySQL dates
    ... I am struggling with some date conversions, ... mapping table for later joins during the search process. ... to assemble a timestamp from the supplied user data, and now I need to look ... between UNIX and mySQL timestamp values. ...
    (php.general)