You will need to handle this in code. Add logic that figures out whether you are in DST or out of DST and then add or subtract the offset. I do not believe that there is any built in function in Oracle that will do this for you. I have done this in the past and had to create conditional statements that contained each years DST start and being dates. I think if you search the ARSList you will find examples of how do this.
On Mon, Aug 24, 2009 at 6:17 PM, Mark Milke<[email protected]> wrote: > Hi David, > > I have a question here. I've tried the following: > > SELECT TO_CHAR(TO_DATE('01/01/1970 00:00:00 GMT', 'DD.MM.YYYY > HH24:MI:SS') + ((outage_time + 3600)/(60*60*24)),'DD.MM.YYYY > HH24:MI:SS') FROM trouble_ticket where tt_nr_ = '0001234567'; > > and it works just fine. > > However depending on what my outage_time is, I'm getting a wrong > result, because of the time zone esp. summer and winter time. Where to > put time zone information here or how to deal with this issue at all? > > > Thanks, > Mark > > > On 18 Aug., 21:02, David Morgan <[email protected]> wrote: >> Hi Lisa >> >> The database reference guide has the information for going from EPOCH >> date to the date in a format you require (in 6.3 it was p.54 - see below >> signature) >> SELECT TO_CHAR(TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY >> HH24:MI:SS') + >> ((C<column_number> + >> <offset>)/(60*60*24)),'MM/DD/YYYY HH24:MI:SS') FROM T<table_number>; >> >> Going the other way (subtract epoch from current stamp to get difference >> in seconds) >> >> selectto_date('2008-07-16','yyyy-mm-dd') >> -to_date('1970-01-01','yyyy-mm-dd') from dual >> >> regards >> Dave Morgan >> >> Converting AR System dates to database dates >> AR System keeps track of the date and time to run escalations, stamps >> requests with the date and time they were submitted, and informs you >> when >> alerts were sent. To track the date and time, AR System uses a format >> that >> measures the number of seconds from January 1, 1970, 12:00 a.m. >> Greenwich >> Mean Time (GMT). While accurate, this format can be an awkward format >> to read. You might want to translate it to a format that your database >> can >> easily read. >> >> Each database requires different commands for the date and time >> conversion. The following procedures describe how you can use your >> database to convert the AR System date and time format. >> Note: In the SQL commands in the following procedures, the column >> number is referenced by <column_number>. Alternatively, you can >> provide the SQL view name of the column (the database name of the field >> as displayed in Remedy Administrator). >> " To convert the date and time format for a DB2 Universal database: >> " See your DB2 documentation for information about dateline arithmetic. >> " To convert the date and time format for an Informix database: >> 1 Using any front-end tool that allows direct access to an Informix-SQL >> database, log in as the root user. >> 2 Type the following command: >> % select (extend((extend(datetime(1970-1-1) year to day, year to hour) - >> interval(<offset_hours>) hour to hour), year to second) + >> C<column_number> >> units second) from T<table_number> >> where, <column_number> is the number of the column for the date and time >> field, <table_number> is the number of the form table, and >> <offset_hours> is >> a positive or negative number representing the number of hours later or >> earlier than GMT. >> If the date is greater than 09/10/2001, you will receive an error. To >> avoid an >> error, you can display minutes instead of seconds by using the following >> command: >> % select (extend((extend(datetime(1970-1-1) year to day, year to hour) - >> interval(<offset_hours>) hour to hour), year to minute) >> +(C<column_number>/60) >> units minute) from T<table_number> >> See the Informix Guide to SQL: Reference and Syntax manaul for >> information >> about the datetime, extend, and interval functions. >> " To convert the date and time format for an Oracle database: >> 1 Using any front-end tool that enables direct access to an Oracle SQL >> database, log in as a user with write access to the AR System tables. >> 2 Type the following command: >> % SELECT TO_CHAR(TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY >> HH24:MI:SS') + ((C<column_number> + <offset>)/(60*60*24)), >> 'MM/DD/YYYY HH24:MI:SS') FROM T<table_number>; >> where, <column_number> is the number of the column for the date and time >> field, <table_number> is the number of the form table, and <offset> is a >> positive or negative number representing the number of seconds later or >> earlier than GMT. >> See the your Oracle documentation for information about theTO_DATEand >> TO_CHAR functions. >> " To convert the date and time format for a Sybase or Microsoft SQL >> Server >> database: >> 1 Using any front-end tool that enables direct access to a Sybase or >> Microsoft >> SQL Server database, log in as a user who has write access to the AR >> System >> tables. >> 2 Type the following command: >> % select dateadd(second, C<column_number> + <offset>, >> "Jan 1, 1970") from T<table_number> >> where, <column_number> is the number of the column for the date and time >> field, <table_number> is the number of the form table, and <offset> is a >> positive or negative number representing the number of seconds later or >> earlier than GMT. >> 3 Optionally, you could format the date field by using the convert >> function. >> There are 12 different formats from which you can choose. See your >> Sybase >> documentation. >> >> -----Original Message----- >> From: Action Request System discussion list(ARSList) >> >> [mailto:[email protected]] On Behalf Of Kemes, Lisa >> Sent: 18 August 2009 18:24 >> To: [email protected] >> Subject: Re: Date Time Conversion Issue in Remedy >> >> --_000_46CDC1E68702E54387E9EAEDC0A02CA207C87BF0C5us194mx002tyc_ >> Content-Type: text/plain; charset="us-ascii" >> Content-Transfer-Encoding: quoted-printable >> >> I think this is the function to convert FROM Epoch to a readable >> date/time = >> format. I need the other way around... >> >> Thanks! >> >> Lisa >> >> ________________________________ >> From: Action Request System discussion list(ARSList) >> [mailto:arsl...@arslis= >> T.ORG] On Behalf Of Cesaro, Tony M[EQ] >> Sent: Tuesday, August 18, 2009 1:01 PM >> To: [email protected] >> Subject: Re: Date Time Conversion Issue in Remedy >> >> ** >> Lisa, >> >> We ended up writing an Oracle function to generate the conversion for >> us. = >> I have included the SQL source below. You'll obviously want to change >> your= >> time zone and input date format accordingly. >> >> create or replace FUNCTION GETDATE (a_number number) >> RETURN Date IS >> date_x date; >> >> BEGIN >> if a_number is null then >> return null; >> else >> date_x :=3D new_time(TO_DATE('01/01/1970 00:00:00', >> 'MM/DD/YYYYHH24:MI= >> :SS') + (a_number/86400),'GMT','EDT'); >> end if; >> RETURN(date_x); >> END GetDate; >> >> Regards, >> >> Tony Cesaro >> >> From: Action Request System discussion list(ARSList) >> [mailto:arsl...@arslis= >> T.ORG] On Behalf Of Kemes, Lisa >> Sent: Tuesday, August 18, 2009 12:52 >> 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 d= >> o you convert a MM/DD/YYYY HH:MM:SS to epoch time. We are using Windows >> 20= >> 03, Oracle 10, ARS 7.0.1.... >> >> I've seen lots of info about converting Epoch to a readable date format, >> bu= >> t not the other way around.... >> >> Lisa >> >> ________________________________ >> From: Action Request System discussion list(ARSList) >> [mailto:arsl...@arslis= >> T.ORG] On Behalf Of Robert Halstead >> Sent: Tuesday, August 04, 2009 4:24 PM >> To: [email protected] >> Subject: Re: Date Time Conversion Issue in Remedy >> ** In the database, Remedy stores all date's in integer format (epoch >> unix = >> time). Perhaps this is your issue? If you are putting these records >> direc= >> tly into the database without going through remedy then you need to use >> epo= >> ch time. If you are going through remedy, then I believe its the locale >> of= >> the server but not sure. (MM/DD/YYYY HH:MM:SS) >> >> Hopefully this helps? >> On Tue, Aug 4, 2009 at 11:31 AM, AMEY BHOSALE >> <[email protected]<mailto:am= >> [email protected]>> wrote: >> ** Hi All, >> >> I have an issue in conversion of Date Time Format which is retrieved by >> cal= >> ling a stored Procedure from other Database into Remedy >> >> So the workflow is there an Filter which has Set Field Action consisting >> of= >> Webservice where the input and Output Parameters are mapped.So this >> webser= >> vice runs on the XMLGATEWAY calling the stored procedure. >> >> There is a form in Remedy which will store the details retrieved from >> store= >> d procedure.The fields in the form are mapped in Xmlgateway Template >> (Creat= >> e) and Template (Query). >> >> The Template(Create) will create the record in the Remedy Form with the >> Out= >> put recieved from the Template(Query) which contains the stored >> procedure d= >> etails. >> >> So there are there date time fields which are returned by stored >> procedure = >> having the formats as below : - >> >> Two Date Time fields have format as :- MM/dd/yyyy hh:mm:ss for e.g >> 4/17/200= >> 8 12:00:00 >> One Date Time field has format as :- yyyy/MM/dd hh:mm:ss for e.g >> 2008/ 4= >> /17 12:00:00 >> >> So in the Create Template of Xmlgateway i specified the above formats >> but w= >> hen the record is created in Remedy the other values are captured but >> the d= >> ate time format fields the value is blank. >> >> When i checked Catalina.out log file found that it is not able to >> convert t= >> his date format into Remedy. >> >> So can anyone let me know what format of date time needs to be specified >> in= >> Create Template of Xmlgateway ? >> >> Regards, >> >> Amey Bhosale >> >> _Platinum Sponsor: >> [email protected]<mailto:[email protected]= >> > ARSlist: "Where the Answers Are"_ >> >> -- >> "A fool acts, regardless; knowing well that he is wrong. The ignoramus >> acts= >> on only what he knows, but all that he knows. >> The ignoramus may be saved, but the fool knows that he is doomed." >> >> Bob Halstead >> _Platinum Sponsor: [email protected] ARSlist: "Where the Answers >> Are= >> "_ >> _Platinum Sponsor: [email protected] ARSlist: "Where the Answers >> Are= >> "_ >> _Platinum Sponsor: [email protected] ARSlist: "Where the Answers >> Are= >> "_ >> >> ________________________________________________________________________ >> _______ >> UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org >> Platinum Sponsor:[email protected] ARSlist: "Where the Answers >> Are" >> >> --_000_46CDC1E68702E54387E9EAEDC0A02CA207C87BF0C5us194mx002tyc_ >> Content-Type: text/html; charset="us-ascii" >> Content-Transfer-Encoding: quoted-printable >> >> ** >> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> >> <HTML xmlns:v =3D "urn:schemas-microsoft-com:vml" xmlns:o =3D=20 >> "urn:schemas-microsoft-com:office:office" xmlns:w =3D=20 >> "urn:schemas-microsoft-com:office:word" xmlns:m =3D=20 >> "http://schemas.microsoft.com/office/2004/12/omml";><HEAD> >> <META http-equiv=3DContent-Type content=3D"text/html; >> charset=3Dus-ascii"> >> <META content=3D"MSHTML 6.00.6000.16890" name=3DGENERATOR></HEAD> >> <BODY lang=3DEN-US vLink=3Dpurple link=3Dblue> >> <DIV dir=3Dltr align=3Dleft><SPAN class=3D051072317-18082009><FONT >> face=3DA= >> rial=20 >> color=3D#000080 size=3D2>I think this is the function to convert FROM >> Epoch= >> to a=20 >> readable date/time format. I need the other way=20 >> around...</FONT></SPAN></DIV> >> <DIV dir=3Dltr align=3Dleft><SPAN class=3D051072317-18082009><FONT >> face=3DA= >> rial=20 >> color=3D#000080 >> >> ... >> >> Erfahren Sie mehr » > > _______________________________________________________________________________ > 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"
