Re: [ORACLE] Timestamp to unix format and viceversa

On 7 Nov 2005 09:25:42 -0800, "GePs" <spammaquesta_at_email.it> wrote:

>Hi everyone!
>Im working with OCI and C language, and I need to convert a values
>fetched from a db table in a unix timestamp (seconds passed by january
>first 1970).
>
>The table is like this
>create table test(.... ts timestamp not null...);
>
>and the code to define the output variable (for a select statement) is
>the following, where time is an OCIDateTime variable
>
>OCIDefineByPos(stmth, &defineh, errh, pos, (dvoid *) time, 0, SQLT_ODT,
> (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT);
>
>once I have initialized "time", how can I convert his content to unix
>timestamp?
>And viceversa, how can I put an int value rapresenting a unix timestamp
>in an OCIDateTime variable, and then insert it correctly inside mt
>table?
>thank you!
>GePs

You are the second one asking this question today

select (sysdate - to_date('01-01-1970','dd-mm-yyyy') * (24*60*60) from dual

will do the trick.

I leave as a homework exercise to you to convert an unix date to an Oracle date.  

--
Sybrand Bakker, Senior Oracle DBA