How to Convert the AIA xref_data Cross Reference table LAST_MODIFIED, LAST_ACCESSED to Date?

advertisements

_____________________________________________________________________________________________________________________

Datatypes for the LAST_MODIFIED and LAST_ACCESSED columns are VARCHAR2. The data stored in the columns are in milliseconds. First of all you have to convert the millisecond values to Days.

So here is the calculation:

1Day is equivalent to ~ 24Hrs * 60 Mins * 60 Secs * 1000(milli)= 86400000

You have to divide the LAST_MODIFIED, LAST_ACCESSED column value by 86400000 then you will get number of days.

Next, you have to add the number days to UNIX time or POSIX time which is equivalent to 01/Jan/1970. Now you are ready with the exact date.

See the example below.

SQL> desc aia.xref_data

 Name                                      Null?    Type

 ----------------------------------------- -------- -------------------

 XREF_TABLE_NAME                           NOT NULL VARCHAR2(2000)

 XREF_COLUMN_NAME                          NOT NULL VARCHAR2(2000)

 ROW_NUMBER                                NOT NULL VARCHAR2(48)

 VALUE                                     NOT NULL VARCHAR2(2000)

 IS_DELETED                                NOT NULL VARCHAR2(1)

 LAST_MODIFIED                             NOT NULL VARCHAR2(100)

 LAST_ACCESSED                             NOT NULL VARCHAR2(100)

SQL> select LAST_MODIFIED, LAST_MODIFIED/86400000 NoofDays, to_char((TO_DATE('01/01/1970','mm/dd/yyyy')+LAST_MODIFIED/86400000),'mm/dd/yyyy hh:mi') Exact_date

from aia.xref_data where rownum<10;

LAST_MODIFIED          NOOFDAYS EXACT_DATE

-------------------- ---------- ----------------

1325849047248        15345.4751 01/06/2012 11:24

1325849047248        15345.4751 01/06/2012 11:24

1325849047251        15345.4751 01/06/2012 11:24

1325849047251        15345.4751 01/06/2012 11:24

1325849047256        15345.4751 01/06/2012 11:24

1325849047256        15345.4751 01/06/2012 11:24

1325849047260        15345.4751 01/06/2012 11:24

1325849047260        15345.4751 01/06/2012 11:24

1325849137180        15345.4761 01/06/2012 11:25

SQL> select count(row_number) from xref_data where trunc((TO_DATE('01/01/1970','mm/dd/yyyy')+LAST_MODIFIED/86400000))= to_date('02/27/2012','mm/dd/yyyy');

COUNT(ROW_NUMBER)

-----------------

           724132

_____________________________________________________________________________________________________________________

Website Stats