SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Number of seconds since 1970 Jan 1 GMT

ysriFeb 3 2013 — edited Feb 5 2013

Hi,
I want to find the number of seconds passed since 1970/01/01 GMT on the oracle server, provided I don't know the timezone of the oracle server

The following query:
SELECT
systimestamp
,sys_extract_utc(systimestamp)
,timestamp '1970-01-01 00:00:00 +00:00'
,sys_extract_utc(systimestamp)-timestamp '1970-01-01 00:00:00 +00:00'
,(sys_extract_utc(systimestamp)-timestamp '1970-01-01 00:00:00 +00:00') * 24 * 60 * 60
FROM DUAL;

fails at the last column (which is the only one I need) with message:
SQL Error: ORA-01873: the leading precision of the interval is too small
01873. 00000 - "the leading precision of the interval is too small"

I am looking for a light weight sql to get seconds passed on the oracle server since epoch, when oracle server timezone is not known

Thanks,
-sri

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 5 2013

Added on Feb 3 2013

5 comments

2,628 views