Thanks again Fred.... When the date/time gets updated in the table, does it have to be in this format to convert over to Epoch?
Dd/mm/yyyy hh:mm:ss. It's going to come over as YYYYMMDD-HHMMSS. Should I reformat it before converting it? Lisa -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Grooms, Frederick W Sent: Tuesday, August 18, 2009 1:12 PM To: [email protected] Subject: Re: Date Time Conversion Issue in Remedy I forgot to add ... If you want to know what time zones your Oracle has you can query the V$TIMEZONE_NAMES view (Oracle 9i or higher) select * from V$TIMEZONE_NAMES Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Grooms, Frederick W Sent: Tuesday, August 18, 2009 12:09 PM To: [email protected] Subject: Re: Date Time Conversion Issue in Remedy Here are my Oracle functions to do the conversion. Change the 'US/Central' to the time zone your database is using. The nice thing about using Oracle to do the GMT offset conversion is it will automatically handle the US Daylight Savings Time adjustments. CREATE OR REPLACE FUNCTION FROM_EPOCH (secs IN NUMBER) RETURN DATE IS lDate DATE; BEGIN lDate := (From_TZ((TO_DATE('01/01/1970', 'dd/mm/yyyy')+(secs/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; / Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Kemes, Lisa Sent: Tuesday, August 18, 2009 11:52 AM To: [email protected] Subject: Re: Date Time Conversion Issue in Remedy I'm having a similar issues (details are a little bit different), but how do you convert a MM/DD/YYYY HH:MM:SS to epoch time. We are using Windows 2003, Oracle 10, ARS 7.0.1.... I've seen lots of info about converting Epoch to a readable date format, but not the other way around.... Lisa _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"
