if you would like to convert the Unix 'seconds since 1970' number into
a meaningful date, it is easy to do if you know your timezone. The
following is in Pro*C but shows the SQL "to_date" and date arithmetic you
would perform to convert this number into an Oracle date:
...
time_t theTime;
varchar otime[50];time(&theTime);
printf( "%s", ctime( &theTime ) );
EXEC SQL SELECT to_char(
new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 * :theTime,
'GMT', 'EDT' ), 'dd-mon-yyyy hh24:mi:ss' )
into :otime from dual;
...Wed May 13 21:35:06 1998
13-may-1998 21:35:06
this will do it in the timezone EDT for example. Instead of
using new_time (in the event it does not handle your time zone), you may
just add "+ (gmt_offset)/24" to the first argument I am passing to new_time.
That will perform the same operation.
If you want the date in a format that is a C time type and you are sure
it'll fit (eg: its after 1/1/1970 and before some time in the future when
the C time type stops working) AND you know your timezone (you can use
a C function to get that) you can use:
static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
time_t t;
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT (new_time(sysdate,'EDT','GMT') -
to_date('01-jan-1970','dd-mon-yyyy')) * (24*60*60)
INTO :t
FROM DUAL;printf( "C Time = %d\n", time(NULL) );
printf( "SQL Time = %d\n", t );
}