Oracle TimesTen Getting Unixtime or Seconds Since the Epoch UTC

On normal oracle databases, you can run a simple sql query subtracting the epoch date from sysdate. This doesn't work for TimesTen as you will get an error mentioning 'An interval data type must be specified for a datetime arithmetic result.' The easiest work around is to cast the subtraction as a bigint and divide by 1000000

On TimesTen - sql 
select cast((sysdate - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) as bigint)/1000000 from dual 

On conventional oracle database - sql 
select (sysdate - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400 from dual

2 comments:

Anonymous said...

For future readers - the division is to deal with the Oracle timestamp being recorded in microseconds, where epoch is in seconds.

1 million ought to do it (rather than the hundred thousand stated or the ten million in the code)

Naviya Nair said...

Very interesting and good Explanation
ASP NET Training
ASP NET Training
ASP NET Online Training
C-Sharp Training
Dot Net Training in Chennai
Online .Net Training


MVC Training
WCF Training
Web-API Training
LINQ Training
Entity Framework
Training

Dot Net Interview Questions