SQL Error: ORA-01873: the leading precision of the interval is too small
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.