When working in Oracle here are the 2 functions I use for Timestamps

CREATE OR REPLACE FUNCTION FROM_EPOCH (lSecs IN NUMBER)
  RETURN DATE
IS
  lDate DATE;
BEGIN
  lDate := (From_TZ((TO_DATE('01/01/1970', 'dd/mm/yyyy')+(lSecs/86400)),'GMT') 
AT TIME ZONE 'US/Central');
  RETURN lDate;
END From_Epoch;



CREATE OR REPLACE FUNCTION TO_EPOCH (lDate IN DATE)
   RETURN NUMBER
IS
   Seconds NUMBER;
   dte1 TIMESTAMP;
   dte2 TIMESTAMP;
BEGIN
   dte1 := new_time(lDate, to_char( from_tz(cast (lDate as timestamp), 
'US/Central'),'TZD'),'GMT');
   dte2 := TO_TIMESTAMP_TZ('01/01/1970 '|| TZ_Offset('Greenwich'), 'MM/DD/YYYY 
TZH:TZM');
   Seconds := (((dte1-0) - (dte2-0)) * 86400);
   RETURN Seconds;
END To_Epoch;


In your case find the Oracle Timezone name for your time zone and replace the 
US/Central with it.  You can find the list of time zones using
  SELECT * FROM gv$timezone_names;

Fred

From: Action Request System discussion list(ARSList) 
[mailto:[email protected]] On Behalf Of Mahendra Mahalkar
Sent: Saturday, June 18, 2011 4:10 AM
To: [email protected]
Subject: After migration oracle reports not matching remedy reports

Hi,
An application for MTBF, MTTR, etc. calculation is developed in ars. Now when I 
import oracle data to ars running the stored procedure for same remedy tables 
by taking unix epoch time into consideration, the reports doesn't match. The 
stored procedure in oracle is running on oracle data with oracle date format, 
and stored procedure on remedy data with unix epoch date format. The results 
are not matching for up_time calculation.
I just change the parameters of dates in stored procedure with e.g. 
to_date(oracle date)-to_date(01011970) function. Do I need to consider 
offset/daylight saving etc.? Though oracle data is for Arabian std.time which 
is same as ars.
Any suggestions/help?

Regards,
Mahendra Mahalkar

Reply via email to