Storing time values via jdbc thin driver

On Nov 29, 9:54 am, "gym dot scuba dot kennedy at gmail"
<kenned....RemoveThis@verizon.net> wrote:

> Actually no. Oracle only knows time and date together. In Oracle there is
> no stand alone time data type.

Strictly speaking, that isn't true; the INTERVAL type can be used to
specify a time of day without reference to any particular date. (The
reason being a time of day is just a period of time - i.e., an
interval - since the previous midnight.)

However, not many people would think to use an INTERVAL in this way.

-- Phil
 >> Stay informed about: Storing time values via jdbc thin driver 

sybrandb.TakeThisOut@hccnet.nl wrote:

> It seems you know everything better, and don't want to follow up any
> advice.

What I have learnt from you: Don't ask a DBA about JDBC.

> Why do you post here at all, if you already know the solution?

Because here are people like Vladimir M. Zakharychev who know the correct
answer.

Thank you.
 >> Stay informed about: Storing time values via jdbc thin driver 

Vladimir M. Zakharychev wrote:

> Answering OP's question: this is a known behavior change between 8i/9i
> and 10g JDBC drivers, documented in the Metalink note 269517.1 and bug
> #3038799. If you are sure you will stick to 10g driver, just update
> all rows so that the date component is Jan 1, 1970, I think this
> should do the trick. If you are going to use different driver
> versions, either stop using literals and start using binds with
> properly initialized date component (the note recommends to always
> initialize a Calendar object to Jan 1st, 1970 and then instantiate
> java.sql.Time from it) or choose a different data type (INTERVAL DAY
> TO SECOND, for example.)


Thank you very much for the detailed explanation!
 >> Stay informed about: Storing time values via jdbc thin driver 

"Vladimir M. Zakharychev" <vladimir.zakharychev.DeleteThis@gmail.com> wrote in message
news:8a27958b-1467-4a68-8c18-e22703377553@h20g2000yqn.googlegroups.com...
On Nov 29, 11:30 pm, hpuxrac <johnbhur....DeleteThis@sbcglobal.net> wrote:
> On Nov 28, 11:02 am, "Chris Seidel" <csei....DeleteThis@arcor.de> wrote:
>
> > Hi,
>
> > I have an Oracle 9.2 database.
> > If I use the 9.2 driver java.sql.Time is stored with 1900-01-01 HH:MM.
> > If I use the 10.2 driver java.sql.Time is stored with 1970-01-01 HH:MM.
>
> > Is this a know bug/feature?
> > Do I have to update all my records to 1970 when using oracle 10 driver?
>
> > Thank you
>
> I don't understand exactly what you are trying to do.
>
> Are you trying to store a time value from an application into a column
> in oracle which is in reality a date datatype in oracle?
>
> If that's the case ... oracle stores both a date and a time in a date
> datatype.
>
> If so ... you need to be careful about a database design where you are
> only using "part" of an oracle datatype.
>
> It is possible to create a char definition of length 8 for a column
> and from the application store in a field that looks like HH:MM:SS
> ( Hour Hour Minute Minute Second Second ).
>
> Of course it is also possible to use only the part of the date
> datatype that has time information and ignore/mask out the date
> portion if you know how to do it.
>
> There is also a datatype known as systimestamp that gives you better
> precision below the second level for a time value ... yes it also has
> a date part that also you can "ditch and/or ignore".
>
> Best place to start might be looking at the Oracle Concepts
> documentation and make sure that you understand the datatypes your
> application is trying to process against.

The trouble the OP has is that Oracle JDBC driver implementations of
the same Java type persistence are different between driver versions.
That this type is initially inappropriate because it is not natively
supported by the database is irrelevant to the case IMO. A lot of
people commented in this thread and all of them seem to agree that
choice of the data type was inappropriate because Oracle does not
support it, but that was not the question asked.

The issue here is that JDBC is supposed to be platform-independent, it
should work the same with any database, at least with *standard* types
(and TIME is a standard *core* SQL data type. By the way, Oracle
documentation claims *full* conformance to the Core SQL:2003 feature
F051 "Basic date and time", which mandates support of the TIME data
type.) Let's face it: Java programmers are not supposed to know
specifics of particular database platform they stuff their data into,
standard APIs should work the same against any database. For them, the
database is just some data storage, they don't really care how it
works and if it's any different from some other database. The database
vendor is expected to provide the driver that takes care of specific
implementation details, and Oracle claims to have provided such
driver. However, different versions of this driver give different
results for the same basic calls using only standard types. Then you
guys jump in and say that TIME is not supported in Oracle - so what?
The JDBC driver should take care of this, and it should do so in
consistent manner not changing between versions (I should stress we
are talking about core standard stuff here.) However, it does not -
its behavior changed since 10g.

Answering OP's question: this is a known behavior change between 8i/9i
and 10g JDBC drivers, documented in the Metalink note 269517.1 and bug
#3038799. If you are sure you will stick to 10g driver, just update
all rows so that the date component is Jan 1, 1970, I think this
should do the trick. If you are going to use different driver
versions, either stop using literals and start using binds with
properly initialized date component (the note recommends to always
initialize a Calendar object to Jan 1st, 1970 and then instantiate
java.sql.Time from it) or choose a different data type (INTERVAL DAY
TO SECOND, for example.)

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Excellent explanation. I have to disagree on part of it. Different RDBMS's
work different ways and Java programers should be aware of the differences.
If not then they are coding for the least common denominator. I know how
they love to be db agnostic, but they aren't language agnostic. (nor should
they be)
Jim
 >> Stay informed about: Storing time values via jdbc thin driver 

On Dec 1, 6:07 am, "gym dot scuba dot kennedy at gmail"
<kenned....TakeThisOut@verizon.net> wrote:
> "Vladimir M. Zakharychev" <vladimir.zakharyc....TakeThisOut@gmail.com> wrote in messagenews:8a27958b-1467-4a68-8c18-e22703377553@h20g2000yqn.googlegroups.com...
>
> Excellent explanation.  I have to disagree on part of it.  Different RDBMS's
> work different ways and Java programers should be aware of the differences.
> If not then they are coding for the least common denominator.  I know how
> they love to be db agnostic, but they aren't language agnostic. (nor should
> they be)
> Jim

You and me know that and advocate for database platform awareness,
among many others. Are we successful thus far? Doesn't look like.
People from the other side of the fence keep creating and advertising
frameworks that completely hide details from programmers and force
them to code for the least common denominator. Is it bad? We think it
is, they think it isn't. We say that by not being aware of what
particular database platform can do and ignoring built-in
functionality they essentially throw their investment into that
platform to the trash bin. They say who cares as long as it works and
development is so rapid and simple and resulting code is fully
platform-independent? (and performs equally bad everywhere, we might
add, but this fact usually becomes obvious too late in the development
cycle and then DBAs are being called in to "fix" the database because
apparently the bottleneck is there - how can it be anywhere else?
In the world where time-to-market figures prevail in measuring project
success we will always be the minority. However, this doesn't mean we
should give up.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
 >> Stay informed about: Storing time values via jdbc thin driver 

On Dec 1, 3:33 am, "Vladimir M. Zakharychev"
<vladimir.zakharyc... DeleteThis @gmail.com>

> You and me know that and advocate for database platform awareness,
> among many others. Are we successful thus far? Doesn't look like.
> People from the other side of the fence keep creating and advertising
> frameworks that completely hide details from programmers and force
> them to code for the least common denominator. Is it bad? We think it
> is, they think it isn't. We say that by not being aware of what

I just heard in the last seven days from a J2EE developer (a really
good one, actually) "I don't want to know SQL".

I asked another J2EE guy a couple of years ago "what happens if you
switch application programming platforms from java?" He stared at me
for a couple of seconds and asked "Why would you want to do that?"
LOL! I still crack up every time I think of that.

Regards,

Steve (Sun Certified Java Programmer & Sun Certified Business
Component Developer )
 >> Stay informed about: Storing time values via jdbc thin driver 

Steve Howard wrote:

> I just heard in the last seven days from a J2EE developer (a really
> good one, actually) "I don't want to know SQL".

Because he knows EQL

> I asked another J2EE guy a couple of years ago "what happens if you
> switch application programming platforms from java?"

Reverse engineering a cryptic db schema created by an OR-mapper
 >> Stay informed about: Storing time values via jdbc thin driver 

Steve Howard wrote:
> On Dec 1, 3:33 am, "Vladimir M. Zakharychev"
> <vladimir.zakharyc... DeleteThis @gmail.com>
>
>> You and me know that and advocate for database platform awareness,
>> among many others. Are we successful thus far? Doesn't look like.
>> People from the other side of the fence keep creating and advertising
>> frameworks that completely hide details from programmers and force
>> them to code for the least common denominator. Is it bad? We think it
>> is, they think it isn't. We say that by not being aware of what
>
> I just heard in the last seven days from a J2EE developer (a really
> good one, actually) "I don't want to know SQL".
>
> I asked another J2EE guy a couple of years ago "what happens if you
> switch application programming platforms from java?" He stared at me
> for a couple of seconds and asked "Why would you want to do that?"
> LOL! I still crack up every time I think of that.
>
When I was advocating (about 12 years ago) a switch from Wang VS PACE to
Oracle, I mentioned to my boss - the IT Director - that, given that we
planned to use Oracle features, and NOT write agnostic code, it might
prove expensive if we subsequently wanted to move from Oracle to a
different database.

He stared at me for a couple of seconds and asked "Why would we want to
do that?"

Palooka
 >> Stay informed about: Storing time values via jdbc thin driver 

Steve Howard, 01.12.2008 15:46:
> I just heard in the last seven days from a J2EE developer (a really
> good one, actually) "I don't want to know SQL".

That's the attitude that I'm trying to fight wherever I can.

Thomas
 >> Stay informed about: Storing time values via jdbc thin driver