Skip to Main Content

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!

SQL Error: ORA-01873: the leading precision of the interval is too small

sree_466May 5 2012 — edited May 6 2012
Hi,

My requirement is to get the current time stamp in Epoch time microsec format.

I am trying to execute the following query

select (CAST(((current_timestamp - TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS'))*24*60*60*1000000) AS varchar(32)) ) as "dt" from dual ;

i am receiving the following error
Error starting at line 1 in command:
select (CAST(((current_timestamp - TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS'))*24*60*60*1000000) AS varchar(32)) ) as "dt" from dual
Error report:
SQL Error: ORA-01873: the leading precision of the interval is too small
01873. 00000 - "the leading precision of the interval is too small"
*Cause: The leading precision of the interval is too small to store the
specified interval.
*Action: Increase the leading precision of the interval or specify an
interval with a smaller leading precision.

but if i tried to do
select (CAST(((sysdate - TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS'))*24*60*60*1000000) AS varchar(32)) ) as "dt" from dual ;
is working fine but i am getting the wrong epochtime as sysdate does not have the timezone information.

please help me how to make the first query work so that i can get the current_timestamp change it to gmt and convert it to Epoch time.
please let me know if there is any other way to get the Epochtime.
This post has been answered by Solomon Yakobson on May 5 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 3 2012
Added on May 5 2012
4 comments
10,026 views