Mihai's Oracle SOA Solutions Blog

This explains how is the TIMESTAMP column from ESB_ACTIVITY table coded, and how to convert it to DATE format if needed.

Structure of ESB_ACTIVITY table is :


SQL> desc ESB_ACTIVITY
Name                                      Null?    Type
----------------------------------------- -------- ---------------------------
ID                                        NOT NULL NUMBER
FLOW_ID                                   NOT NULL VARCHAR2(256)
SUB_FLOW_ID                                        VARCHAR2(48)
SEQ                                                NUMBER
SUB_FLOW_SEQ                                       NUMBER(3)
BATCH_ID                                           VARCHAR2(48)
SOURCE                                             VARCHAR2(48)
OPERATION_GUID                                     VARCHAR2(48)
TIMESTAMP                                 NOT NULL NUMBER
TYPE                                      NOT NULL NUMBER(2)
RR_OUTPUT_STATUS                                   NUMBER(2)
ADDI_INFO                                          VARCHAR2(500)
IS_STALE                                           VARCHAR2(1) 


The values from TIMESTAMP from ESB_ACTIVITY are coded as Unix timestamps.

The Timestamp column is of type NUMBER and it uses the following formula to calculate it (for example to tranform SYSDATE to TIMESTAMP used in ESB_ACTIVITY) :

Timestamp := ((TRUNC(SYSDATE)) - TO_DATE('01/01/1970','MM/DD/YYYY'))*24*60*60*1000;

In order to get the DATE in PL/SQL, you have to make the reverse operation.


Another simpler solution in Linux shell , is to first Divide the TIMESTAMP by 1000 and next use date function , for example :

date -d @1233200000

For more methods how to transform timestamp to readable date see more here