How do you pass C time_t datetime to Oracle?

[Don Chambers]

I am writing my first C++ UNIX program that uses an Oracle database.
I am writing the middle tier componet and am having trouble with
datetime values.
.
I will be passed a time_t and must return a time time_t. I am not
able to get the time_t into the database as a DATE or select a
date/time and covert it to the time_t.

Can I insert this format into Oracle directly? I could not find this
format for the TO_DATE function.

How do you handle this?

I have written a couple functions to do the conversions using a
variety of methods and seem to be off because of time zones. I would
think that this is a common task and should be relativly easy.

This time_t variable comes from a windows system where the time is
actually an unsigned long that is the number of seconds from Jan. 1,
1970 00:00:00. In the middle tier I am using Pro*C/C++. Does pro C
provide a conversion for this?

Thanks,
Don
cham...@inquiregroup.com

[Sybrand Bakker]

date columns are treated a strings in C.

Hth,

Sybrand Bakker, Oracle DBA

Don Chambers <dcha...@mindspring.com> schreef in berichtnieuws
ikqpgs8m3pcqp0lmr...@4ax.com...

[Thomas J. Kyte]

In article <ikqpgs8m3pcqp0lmr...@4ax.com>,

Don Chambers <dcha...@mindspring.com> wrote:
> I am writing my first C++ UNIX program that uses an Oracle database.
> I am writing the middle tier componet and am having trouble with
> datetime values.
> .
> I will be passed a time_t and must return a time time_t. I am not
> able to get the time_t into the database as a DATE or select a
> date/time and covert it to the time_t.
>
> Can I insert this format into Oracle directly? I could not find this
> format for the TO_DATE function.
>
> How do you handle this?
>
> I have written a couple functions to do the conversions using a
> variety of methods and seem to be off because of time zones. I would
> think that this is a common task and should be relativly easy.
>
> This time_t variable comes from a windows system where the time is
> actually an unsigned long that is the number of seconds from Jan. 1,
> 1970 00:00:00. In the middle tier I am using Pro*C/C++. Does pro C
> provide a conversion for this?
>
> Thanks,
> Don
> cham...@inquiregroup.com
>

see http://osi.oracle.com/~tkyte/Misc/CTime.html

--
Thomas Kyte tk...@us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy.

[Mark D Powell]

In article <957173205.17324....@news.demon.nl>,

"Sybrand Bakker" <pos...@sybrandb.demon.nl> wrote:
> date columns are treated a strings in C.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> Don Chambers <dcha...@mindspring.com> schreef in berichtnieuws
> ikqpgs8m3pcqp0lmr...@4ax.com...

> > I am writing my first C++ UNIX program that uses an Oracle database.
> > I am writing the middle tier componet and am having trouble with
> > datetime values.
> > .
> > I will be passed a time_t and must return a time time_t. I am not
> > able to get the time_t into the database as a DATE or select a
> > date/time and covert it to the time_t.
> >
> > Can I insert this format into Oracle directly? I could not find
this
> > format for the TO_DATE function.
> >
> > How do you handle this?
> >
> > I have written a couple functions to do the conversions using a
> > variety of methods and seem to be off because of time zones. I
would
> > think that this is a common task and should be relativly easy.
> >
> > This time_t variable comes from a windows system where the time is
> > actually an unsigned long that is the number of seconds from Jan. 1,
> > 1970 00:00:00. In the middle tier I am using Pro*C/C++. Does pro C
> > provide a conversion for this?
> >
> >
> > Thanks,
> > Don
> > cham...@inquiregroup.com
> >
>

I can not guarentee this is correct since I do not write C, but I gave
this to someone once before who was using a reference date and they
responded that it worked for them:
rem
rem Convert Oracle date to number of seconds since reference date
rem
rem 20000412 Mark D Powell New
rem
select to_number(sysdate - to_date('01-JAN-1970','DD-MON-YYYY'))
* (24 * 60 * 60) "C Time"
from sys.dual
/
rem
rem Convert from number of seconds since reference date to Oracle date
rem
select to_char(to_date('01-JAN-1970','DD-MON-YYYY') +
( &no_sec_since_70 / (60 * 60 * 24) ),'DD-MON-YYYY HH24:MI:SS')
"New Date"
from sys.dual

If you run the above the second sql will convert the value returned by
the first sql back to the current date/time so it looks like it works,
but I am not a date/time expert.

--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --