You might modify what you propose. Use varchar2 not char and use the server
to put in the timestamp not the client. That way the time is consistent and
not different because each client might have a different time drift. You
can use Java in the database to create a function to return the timestamp in
whatever format you want.
Jim
"Ed Stevens" <spamdump_at_nospam.noway.nohow> wrote in message
news:3d2ae375.2546772_at_ausnews.austin.ibm.com...
> On Mon, 08 Jul 2002 19:25:51 GMT, spamdump_at_nospam.noway.nohow (Ed Stevens)
> wrote:
>
> >We are converting an application (written in COBOL) from DB2 to Oracle.
On this
> >project I will be working as both the primary support DBA and a member of
the
> >programming team. So unlike another project I'm on (see "venting my
spleen")
> >I'll be in a very good position to influence application design
decisions.
> >
> >Since Oracle's DATE will only resolve time to seconds, it would appear
that the
> >TIMESTAMP fields will have to become either CHAR or NUMBER datatypes
(with an
> >application-understood format, and few lines of code coming and going to
handle
> >it) I'm looking for best ideas to get the milliseconds, or a resonable
> >facsimilie thereof.
> >
> >I searched the archives and got the general answer of using a sequence.
Even
> >found where I had posted a msg on the same general subject a year ago,
but the
> >replies were a bit more vague than what I'm looking for here. One
message, on
> >another thread, even said to "use the millisecond portion of the
sequence."
> >That's a new one on me.
> >
> >It would appear that the potential exists for needing to increment the
sequence
> >on the order of magnitude of several dozen to a few score times per
second.
> >
> >Any thoughts on best practices for this situation?
> >--
> >Ed Stevens
> >(Opinions expressed do not necessarily represent those of my employer.)
>
> As a self-followup . . .
>
> Didn't know that 9i had a TIMESTAMP type, but we won't get there until
next
> year. This year's task is to get to 8.1.7.
>
> But we did get the solution, and it didn't involve using sequences. Once
I
> started thinking like a programmer again, it became obvious. Make a call
to a
> windows API that returns the date and time resolved to milliseconds, then
use
> the result to build our own timestamp in a CHAR field. DOH!
>
> Kind of scary in my dotage. When I was a programmer, it wouldn't have
taken me
> a half-second to see that solution. But I haven't done any serious coding
since
> I installed our first Oracle db at 7.3. Something there about when your
only
> tool is a hammer, all problems look like nails.
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)
Received on Tue Jul 09 2002 - 09:30:33 CDT
