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

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.
Received on Mon Aug 23 1999 - 19:42:08 CDT