SQL & PL/SQL

Hi

We are having a column in our table as create_ts which is the time, the row is updated. We are in need to get this time in Pro*c as unix timestamp type to find out the oldest row from a set of rows.

I found that the following query works when I convert to date and find out the difference.
select TO_NUMBER((TO_DATE (TO_CHAR (CREATE_TS, 'YYYY-MON-DD HH24:MI:SS'),'YYYY-MON-DD HH24:MI:SS') - to_date('01.01.1970 00:00:00','dd.mm.yyyy HH24:mi:ss')) * 24 * 60 * 60 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600 ,9999999999) as timestamp from customer;

However when I keep it as timestamp type I'm getting some error
select TO_NUMBER((CREATE_TS - to_timestamp('01.01.1970 00:00:00','dd.mm.yyyy HH24:mi:ss'))* 24 * 60 * 60 *1000 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600000 ,9999999999999) as timeints from customer;

ERROR
---------
ORA-30081: invalid data type for datetime/interval arithmetic
30081. 00000 - "invalid data type for datetime/interval arithmetic"

FYI: since if I convert to date type, we're losing the microseconds, we had to find the difference using the timestamp type.


thanks