In Oracle you can convert it using the following NEW_TIME(SYS_EXTRACT_UTC(datetime_with_timezone), 'GMT', 'US/Eastern')
So it would be SELECT NEW_TIME(SYS_EXTRACT_UTC(column), 'GMT', 'US/Eastern') from table I haven’t checked lately, but I know that previously not all of the actions performed by web services were in the logs (especially when they dealt with sub or child form records) Fred From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Joe Martin D'Souza Sent: Thursday, March 29, 2012 3:55 PM To: [email protected] Subject: Re: Sort of OT: Question for timezone experts out there.. ** Thanks. I just had a colleague confirm what you just said.. the –4:00 is to get the Greenwich time, and that 12:05:05 AM is the actual local time that the delete transaction happened.. Are there any Remedy processes that can possibly happen that might escape the probe of these 4 internal logs I turned on - API, SQL, Filter and Escalation ??? I want to make a possible list of every possible way this may be happening.. Joe From: Shellman, David<mailto:[email protected]> Sent: Thursday, March 29, 2012 4:37 PM Newsgroups: public.remedy.arsystem.general To: [email protected]<mailto:[email protected]> Subject: Re: Sort of OT: Question for timezone experts out there.. ** I'm not an expert at the DB timestamps. The way I read the data though is that it is 27 Mar 2012 at 12:05:05 AM and the database is set with a 4 hour offset from UTC (Daylight Savings). Dave ________________________________ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Joe Martin D'Souza Sent: Thursday, March 29, 2012 4:29 PM To: [email protected] Subject: Sort of OT: Question for timezone experts out there.. ** We are having an issue with our Remedy application, where it appears that there is an update on our ‘CTM:People Permission Groups’ form where certain entries are ‘disappearing mysteriously’ apparently with no explanation as to what's causing them to disappear. I turned on API, SQL, Escalation and Filter logging, and nothing traps any activity of any deletes on that form. Which led me to suspect that this might be happening from ‘outside’ of the AR System API layer.. I was told by some of the developers that were on site here before me that they can swear there is nothing outside the application layer that performs any updates or deletes for that matter to our Remedy applications.. However since I cannot get anything through logging, I decided to lay a trap by creating a DB trigger, to capture the DB User and the DB time that these deletes are happening by creating an external audit table on the T table that the CTM:People Permission Groups records are stored.. The time field column I defined in Oracle as TIMESTAMP WITH TIME ZONE.. I need help to interpret the time displayed in this column.. It is displayed as: 27-MAR-12 12.05.05.118256 AM –04:00 How would I convert that to EST? Is it 27-MAR-12 01:05:05 AM EST ??? Is it 26-MAR-12 11:05:05 PM EST ??? Or is it 27-MAR-12 12:05:05 AM EST ??? Joe _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
