how to get time in millisecond from date field of oracle for the date 01-01-9999

Question

I want to get milliseconds from date field of oracle for date "01-01-9999".

I have created below block to achieve the same.

set serveroutput on;
declare
    base_point constant timestamp := to_timestamp_tz('01-JAN-1970 00:00:00.000+00:00', 'DD-Mon-RR HH24:MI:SS.FFTZH:TZM') AT TIME ZONE 'UTC';
    now timestamp := to_timestamp_tz('01-01-2099 00:00:00.000+00:00', 'DD-MM-RR HH24:MI:SS.FFTZH:TZM') AT TIME ZONE 'UTC';
    -- now constant timestamp := systimestamp AT TIME ZONE 'UTC' ;
    n number;
begin

  select to_timestamp_tz(to_char(todate,'DD-MM-YY HH24:MI:SS')||'.000+00:00','DD-MM-YY HH24:MI:SS.FFTZH:TZM')
  into now
   from t_table where ACCOUNTID = 'ACC001124211';

 DBMS_OUTPUT.put_line(' now :'||now);

 n := (
                  ((extract(day    from (now-base_point)))*86400)
                + ((extract(hour   from (now-base_point)))*3600)
                + ((extract(minute from (now-base_point)))*60)
                + ((extract(second from (now-base_point))))
           ) * 1000;

           DBMS_OUTPUT.put_line(' n :'||n);
end;
/

but using above block I am getting value as 4070908800000, which is equal to date 1/1/2099 but actual date in my table is 01-01-9999

Can you please help us to get exact millisecond using date field

Solution 1

No need of PL/SQL, you could do it in plain SQL.

To convert a date to milliseconds since 01-JAN-1970:

SQL> SELECT to_number(DATE '9999-01-01'
  2         - to_date('01-JAN-1970','DD-MON-YYYY')) * (24 * 60 * 60 * 1000) milliseconds
  3  FROM dual;

      MILLISECONDS
------------------
   253370764800000

SQL>

Solution 2

I've posted here some methods to convert timestamp to nanoseconds and nanoseconds to timestamp. These methods are not affected by time zones and have a nanosecond precision.

You just need to adjust it to get milliseconds instead of nanoseconds. You need to convert date to timestamp using "CAST(DATE_HERE AS TIMESTAMP)".

SELECT (EXTRACT(DAY FROM (
    CAST(SYSDATE AS TIMESTAMP) --Replace line with desired timestamp --Maximum value: TIMESTAMP '3871-04-29 10:39:59.999999999 UTC'
- TIMESTAMP '1970-01-01 00:00:00 UTC') * 24 * 60) * 60 + EXTRACT(SECOND FROM
    CAST(SYSDATE AS TIMESTAMP) --Replace line with desired timestamp
)) *  1000 AS MILLIS FROM DUAL;

MILLIS
1598447857000

Solution 3

The reason why you are getting wrong value is this statement.

select to_timestamp_tz(to_char(todate,'DD-MM-YY HH24:MI:SS')||'.000+00:00','DD-MM-YY HH24:MI:SS.FFTZH:TZM')

Since your format element for year is YY, to_char conversion will have only 2 digits for year.

select to_char(date'9999-01-01','DD-MM-YY HH24:MI:SS')||'.000+00:00'    char_date
from dual

char_date
------------
01-01-99 00:00:00.000+00:00

When you convert this to timestamp using YY as format element, the year returned always has the same first 2 digits as the current year, which is why you get 2099 as year.

select to_char(to_timestamp_tz(to_char(date'9999-01-01','DD-MM-YY HH24:MI:SS')||'.000+00:00','DD-MM-YY HH24:MI:SS.FFTZH:TZM'),'yyyy') char_date
from dual;

char_date
------------
2099

Moral of the story:

Oracle recommends that you use the 4-digit year element (YYYY) instead of the shorter year elements for these reasons:

• The 4-digit year element eliminates ambiguity.
• The shorter year elements may affect query optimization because the year is not known at query compile time and can only be determined at run time.