Re: How to convert Oracle Database Date Type to C time_t

Thanks a lot, the reason of why I must to do it is: there are many ORACLE date in the database, and need to retrieve them out and convert them to time_t type . To a big project, it's more efficient if there is a way to do this directly. otherwise, everytime must call a routine to do this.

Ellen

In article <37C1EA60.25BD_at_swbell.net>,
  mck9_at_swbell.net wrote:
> ellen111_at_my-deja.com wrote:
> >
> > but is there time_t date type in pro*C?
> > I got the code from this forum, is it because I didn't
> > include some header filer?
>
> Pro*C comprises both C (which provides a time_t type) and SQL
> (which provides a date type). So far as I know, Pro*C does not
> provide a way to map directly from a date to a time_t, nor vice
> versa. It would be very difficult for Pro*C to do so, because the
> precompiler would have to know the actual type and encoding of a
> time_t for the particular C compiler which was in use.
>
> The internal format of an Oracle date type is a seven-byte structure
> as described in Oracle's documentation. I'm not sure if there's a
> way to extract it in that format, but I can't imagine why you'd want
> to. Use the TO_CHAR function to format the date type into whatever
> character format is convenient. Translate the various pieces into
> the members of a struct tm and pass it to mktime().
>
> That will be a bit tedious, but no more tedious than translating the
> raw 7-byte structure would be, and a lot easier to do correctly.
>
> Michael Cadot has suggested a variation on your original approach,
> but neither version is portable. They both assume a particular way
> of implementing a time_t. Writing it portably is no harder. I can't
> say which would be more efficient, but I wouldn't expect it to make
> a lot of difference.
>
> The <time.h> header contains a declaration of the time_t type and a
> prototype for the mktime() function. If you don't #include this
> header the precompiler may not be able to make sense of your code.
>
> Scott McKellar mck9_at_swbell
> Free PL/SQL utilities at http://home.swbell.net/mck9/pls
> Just out: new release of plsb, a beautifier for PL/SQL
>
> > In article <37BCA9E8.1EC8_at_swbell.net>,
> > mck9_at_swbell.net wrote:
> > > Michel Cadot wrote:
> > > >
> > > > Ellen,
> > > >
> > > > I think you're wrong with operations on date type.
> > > > Your select can be (t as long):
> > > > select
> > > > to_number( to_char(datecol,'J'))*24*60*60+to_number(to_char
> > (datecol,'SSSSS')) -
> > > > (to_number(to_char(to_date
> > ('01/01/1970','DD/MM/YYYY'),'J'))*24*60*60
> > > > +to_number(to_char(to_date
> > ('01/01/1970','DD/MM/YYYY'),'SSSSS')) )
> > > > into :t
> > > > from ...;
> > > >
> > > > This gives you the number of seconds since 01/01/1970.
> > > > Has time_t type a particuliar structure?
> > >
> > > No. The encoding you describe is common, but it is not required
by
> > > the C Standard, which requires only that a time_t be an arithmetic
> > > type capable of representing times. For example it could be a
long
> > > double representing the number of days since the birth of Brian
> > > Kernighan (one of the co-inventors of C).
> > >
> > > As a result there's no way that Oracle could reliably map a date
type
> > > to a time_t for all environments.
> > >
> > > The simplest and most portable way to do the translation is: let
> > > Oracle format the date and time into the various components of a
> > > struct tm and then call mktime().
> > >
> > > Scott McKellar mck9_at_swbell.net
> > > Free PL/SQL utilities at http:://home.swbell.net/mck9/pls/
> > >
> > > > ellen111_at_my-deja.com a écrit dans le message <7pgnl5
> > $kgj$1_at_nnrp1.deja.com>...
> > > > >Hi, when I try to retrieve date from oracle database, and
convert
> > it
> > > > >to time_t type, I have some questions.
> > > > >
> > > > >the way now I used is:
> > > > >select date into char from oracle database, then convert char
> > > > >to time_t(I wrote a function by myself). and it is not very
> > efficient.
> > > > >
> > > > >I was told that there is a way to convert them directly:
> > > > >select date into time_t(? or long, because there is no time_t
date
> > > > >type in pro*C), following is my code:
> > > > >
> > > > >EXEC SQL BEGIN DECLARE SECTION;
> > > > >time_t t; //this got an error when precompile
> > > > >long t; //?
> > > > >EXEC SQL END DECLARE SECTION;
> > > > >....
> > > > >EXEC SQL SELECT ((new_time(date,'EST','GMT') -
> > > > > to_date('01-jan-1970','dd-mon-yyyy'))*24*60*60)
> > > > > INTO :t
> > > > > FROM .....
> > > > >printf("time=%d\n",t);
> > > > >
> > > > >but it returns 0 to me...
> > > > >any advises please!
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Aug 24 1999 - 01:03:28 CDT