Please use the Apache issue tracking system for new NetBeans issues (https://issues.apache.org/jira/projects/NETBEANS0/issues) !!
Bug 49994 - [60cat][40cat] Oracle dates should be treated as timestamps
[60cat][40cat] Oracle dates should be treated as timestamps
Status: RESOLVED FIXED
Product: db
Classification: Unclassified
Component: SQL Editor
4.x
PC Windows XP
: P3 (vote)
: 6.x
Assigned To: David Vancouvering
issues@db
http://forums.oracle.com/forums/threa...
4.2-candidate
:
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2004-10-06 10:54 UTC by dynamite
Modified: 2007-11-08 20:31 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description dynamite 2004-10-06 10:54:24 UTC
[ BUILD # : 200409220845 ]
[ JDK VERSION : J2SE 1.5.0 ]

I am using the oracle 9.2 JDBC driver. Dates are
appearing in retrieved data as dates. However, in
Oracle 'dates' also hold time information and so
what is actually needed is for 'dates' to be
treated as timestamps and for date and time to be
displayed.

Sometimes of course the field will mean time and
other times it will mean dates and at other times
it will mean a combination of both. There is no
apriori way of knowing. However, 1st Jan 1970 is
the default date and almost without exception
means that only the time part is being used. So,
it the date is 1st Jan 1970, only display the time
part.
Comment 1 Pavel Flaska 2005-03-04 16:40:42 UTC
Changing to ENHANCEMENT, as this is not really bug. You are right, it
is possible to detect when only time is set. But I'm not sure how to
handle timezones. It is not mandatory to save the date in GMT and
then, when you are in GMT-1, getTime() call on column '1070-01-01
0:00:00.0' returns -360000, doesn't it?
Comment 2 dynamite 2005-09-28 12:00:48 UTC
Hmmm, see your point.

I thought I might have a look at the code to maybe create a patch that at least
works for my specific dataset, but I am unsure where to look first. I would have
thought that the SQL would return timestamps, but in my exploration of the code
so far I cannot see why they would get treated as dates.

Could you please advise me as to which are the main classes to look at for
database querying and SQL results displaying?
Comment 3 Andrei Badea 2005-09-29 10:14:32 UTC
Sure. The code is in db/core in the
org.netbeans.modules.db.sql.execute.ResultSetTableModelSupport class. It is a
helper class which creates a ResultSetTableModel, which is a TableModel for the
results JTable. Try to implement your own ColumnTypeDef for dates and return
whatever you think should be displayed for dates from your impl of the
getColumnValue() method.
Comment 4 Andrei Badea 2007-10-23 14:00:38 UTC
This is a valid defect, we are not doing the right thing for Oracle. If I understand correctly there are two aspects:

1. displaying the time part (if one is present) along with the date part.
2. not displaying the date part if only the time part is present.

#1 looks doable *if* the Oracle driver returns the time information, which I still need to check (java.sql.Date seems to
claim it doesn't care about time information.). I only plan to display the time part if there is one (i.e., '00:00:00'
will never be displayed. This is in order to avoid displaying a time part on databases for which the DATE type only
holds a date part.

#2 is not trivial, since for a time-only date value the date part is the first day of the current month of the current
year. So for such values I will just display both the date and time part. Not a big inconvenience I think, because if
you use time-only date values you know about it, so you will know to ignore the date part.

Makes sense?
Comment 5 dynamite 2007-10-23 14:05:25 UTC
What I am finding is that I only ever see the date part.  However, when the time is only important then you find that
this is on 1970-01-01 and so if possible it is best if timestamps on that day are converted to be just times.
Comment 6 dynamite 2007-10-23 14:11:57 UTC
The key thing to remember about Oracle is that its Date is, or should be, mapped to java.sql.Timestamp as it has both
date and time parts.  It has not date-only type or time-only type.  As far as I can make out the Oracle driver does give
the correct Java type, so I'm not sure why NetBeans only displays the date.
Comment 7 dynamite 2007-10-23 16:00:23 UTC
It seems that the Oracle driver in Oracle v8 returned timestamp for dates whereas for later versions it returns date. 
This is unfortunate.  It seems that setting -Doracle.jdbc.V8Compatible=true for NetBeans reverts this behaviour to that
of the old version.  Apparently, this has also been hit with glassfish
http://wiki.netbeans.org/wiki/view/VwpFAQOracleSqlDate .

Could NetBeans get the Oracle driver to have oracle.jdbc.V8Compatible set without requiring the user to have such
detailed knowledge?
Comment 8 Andrei Badea 2007-10-25 11:01:00 UTC
> Could NetBeans get the Oracle driver to have oracle.jdbc.V8Compatible set
> without requiring the user to have such detailed knowledge?

That seems too risky. I would rather prefer calling getTimestamp() instead of getObject(). That would, even for DATE
columns, return a java.sql.Timestamp object, which NetBeans display correctly (both the date and time part).

Out of my curiosity, how do you work with the time part of DATE columns in Java applications? Do you use the
V8Compatible flag?
Comment 9 David Vancouvering 2007-10-26 00:20:26 UTC
I agree with Andrei, we should just use getTimestamp() rather than getObject(), since we're changing the code anyway. 
The v8compatible flag is for applications where making the change over to getTimestamp() is not possible, as I
understand it.
Comment 10 David Vancouvering 2007-11-03 20:59:56 UTC
Working on this
Comment 11 David Vancouvering 2007-11-03 21:02:35 UTC
Fixed this issue.  I had to pass down DatabaseMetaData to ResultTableModelSupport so it could determine whether we were
talking to Oracle.  All unit tests pass in core (I had to fix a properties file).

Checking in src/org/netbeans/modules/db/sql/execute/ui/ResultSetTableModel.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/ui/ResultSetTableModel.java,v  <--  ResultSetTableModel.java
new revision: 1.8; previous revision: 1.7
done
Checking in src/org/netbeans/modules/db/sql/execute/ui/SQLResultPanelModel.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/ui/SQLResultPanelModel.java,v  <--  SQLResultPanelModel.java
new revision: 1.6; previous revision: 1.5
done
Checking in src/org/netbeans/modules/db/sql/execute/SQLExecutionResult.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/SQLExecutionResult.java,v  <--  SQLExecutionResult.java
new revision: 1.7; previous revision: 1.6
done
Checking in src/org/netbeans/modules/db/sql/execute/ResultSetTableModelSupport.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/ResultSetTableModelSupport.java,v  <--  ResultSetTableModelSupport.java
new revision: 1.11; previous revision: 1.10
done
Checking in test/unit/data/mysql5.0/dbprop.properties;
/cvs/db/core/test/unit/data/mysql5.0/dbprop.properties,v  <--  dbprop.properties
new revision: 1.4; previous revision: 1.3
done
Comment 12 David Vancouvering 2007-11-05 00:47:38 UTC
Adjustments based on Andrei's feedback.  All core unit tests pass...

Checking in ResultSetTableModelSupport.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/ResultSetTableModelSupport.java,v  <--  ResultSetTableModelSupport.java
new revision: 1.12; previous revision: 1.11
done
Comment 13 dynamite 2007-11-05 09:33:08 UTC
Thanks for your efforts, but alas the fix doesn't work.  What I get is the time always being displayed as 00:00:00.  It
seems that only a date has been retrieved from the server and then we request rendering it with a time part, which is
then just midnight.
Comment 14 David Vancouvering 2007-11-05 13:28:45 UTC
I am sorry to hear it does not work for me.  In the famous words of Joe Debugger: "It Works For Me" :)

There appears to be some dynamics here that I am not yet sure of.  Either my testing environment is broken somehow, or
there are conditions I am unaware of that determine whether the time part is printed out correctly.

I will try to track this down.
Comment 15 dynamite 2007-11-05 13:37:36 UTC
Ah, we've all been there...

Perhaps it is worth verifying that you haven't left -Doracle.jdbc.V8Compatible=true set anywhere.

If it helps, my current driver is for Oracle's 10.2.0 and I was connecting to a 10.2.0 server.
Comment 16 Andrei Badea 2007-11-05 14:29:51 UTC
Second attempt.

Checking in src/org/netbeans/modules/db/sql/execute/ResultSetTableModelSupport.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/ResultSetTableModelSupport.java,v  <--  ResultSetTableModelSupport.java
new revision: 1.13; previous revision: 1.12
done
Checking in src/org/netbeans/modules/db/sql/execute/ui/ResultSetTableModel.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/ui/ResultSetTableModel.java,v  <--  ResultSetTableModel.java
new revision: 1.9; previous revision: 1.8
done
Comment 17 David Vancouvering 2007-11-05 14:45:11 UTC
OK, I see what the problem is.  I have two connections, with two different driver versions.  With the Oracle 9.2.0.5
driver, getTimestamp() returns the full date and time .  For the Oracle 10.2.0.3.0 driver, getTimestamp() does not
deliver the time part.  I also tried it with the 10.1 driver.  These are *all* running against the same Oracle server,
version 10.2.0.1.0.

I even tried "select to_timestamp(mydate) from TRAVEL.TESTDATE" and got the same results -- actually it made things
worse, because the time part was lost even with the Oracle 9 driver.

All the existing documentation that I found through Google, including the one you attached
(http://wiki.netbeans.org/wiki/view/VwpFAQOracleSqlDate), seems to indicate that getTimestamp() should work, but
apparently it does not with newer versions.

When I started NetBeans with -Doracle.jdbc.V8Compatible=true it worked great with all versions of the driver.

Unless anyone has some ideas about how we can get the full and time out of Oracle with the newer drivers, this seems
like a bug/"feature" with Oracle, and not something we can really fix.  I think Oracle is trying to "gently" encourage
users to move to the SQL ANSI standard and use TIMESTAMP to store date and time.

I suggest setting -Doracle.jdbc.V8Compatible=true or altering your table to be a timestamp type rather than a date type?
 But at least with this fix it is *possible* to get the timestamp out if you set the V8Compatible flag.


Comment 18 David Vancouvering 2007-11-05 14:57:36 UTC
I tried it with Andrei's changes, same behavior.  The source of the confusion is that with the Oracle 9.2.0.5 driver,
getTimestamp() returns the full date and time .  For the Oracle 10.2.0.3.0 driver, getTimestamp() does not deliver the
time part.  I also tried it with the 10.1 driver.  These are *all* running against the same Oracle server, version
10.2.0.1.0.

I even tried "select to_timestamp(mydate) from TRAVEL.TESTDATE" and got the same results -- actually it made things
worse, because the time part was lost even with the Oracle 9 driver.

All the existing documentation that I found through Google, including the one you attached
(http://wiki.netbeans.org/wiki/view/VwpFAQOracleSqlDate), seems to indicate that getTimestamp() should work, but
apparently it does not with newer versions.

When I started NetBeans with -Doracle.jdbc.V8Compatible=true it worked great with all versions of the driver.

Unless anyone has some ideas about how we can get the full and time out of Oracle with the newer drivers, this seems
like a bug/"feature" with Oracle, and not something we can really fix.  I think Oracle is trying to "gently" encourage
users to move to the SQL ANSI standard and use TIMESTAMP to store date and time.

I suggest setting -Doracle.jdbc.V8Compatible=true or altering your table to be a timestamp type rather than a date type?
 But at least with this fix it is *possible* to get the timestamp out if you set the V8Compatible flag, so we definitely
shouldn't back it out.
Comment 19 David Vancouvering 2007-11-05 14:59:44 UTC
Sorry for the duplicate entries, issuezilla told me that my change was lost due to conflict with Andrei's comment, but
apparently not the case.  

Summary:

- Andrei's change apparently doesn't change things for newer drivers, same problem
- Setting the V8Compatible flag makes things work
- Recommend users alter their columns to TIMESTAMP if they can, as that is what Oracle appears to be encouraging.
Comment 20 Andrei Badea 2007-11-05 15:25:03 UTC
My change works fine for me with Oracle 10.2.0.1.0 and driver 10.2.0.1.0. If it doesn't work with some drivers, well,
there's not much we can do here. But I expect users won't use such drivers much, as their applications won't work with
those drivers either (unless using the V8Compatible property, that is).
Comment 21 David Vancouvering 2007-11-05 17:55:55 UTC
It turns out that the reason Andrei's change wasn't working for me was because I had assumed a clean build under the
'db' directory included the 'core' module, but it doesn't.

When I did a fresh build of the 'core' module separately, it works for me too with the latest drivers.  

Thanks to for getting this to work in time for 6.0. Code freeze happens tonight, so this went in under the wire.
Comment 22 dynamite 2007-11-06 10:16:10 UTC
Thank you for all your efforts.  However I do have the driver 10.2.0.1.0 driver working against an Oracle 10.2.0.1.0
database and it still isn't working for me.  Is there something else I need to do?  Re-register the driver in NB?  Clear
some caching?
Comment 23 David Vancouvering 2007-11-06 12:38:24 UTC
Are you testing with a download of the nightly build or building NetBeans yourself?  If the former, when did you get the
build?  If the latter, how are you rebuilding?
Comment 24 dynamite 2007-11-06 13:12:25 UTC
Ah, that's it.  I downloaded the latest build this morning, but I now see an even later one is there which when tested
works!

Thanks for all your efforts

Dan


By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2014, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo