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