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<mark_mi...@yahoo.com> 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 <dave.mor...@tiberone.com> 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:arsl...@arslist.org] On Behalf Of Kemes, Lisa
>> Sent: 18 August 2009 18:24
>> To: arsl...@arslist.org
>> 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: arsl...@arslist.org
>> 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: arsl...@arslist.org
>> 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: arsl...@arslist.org
>> 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
>> <ameyb...@gmail.com<mailto:am=
>> eyb...@gmail.com>> 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:
>> rmisoluti...@verizon.net<mailto:rmisoluti...@verizon.net=
>> > 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: rmisoluti...@verizon.net ARSlist: "Where the Answers
>> Are=
>> "_
>> _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers
>> Are=
>> "_
>> _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers
>> Are=
>> "_
>>
>> ________________________________________________________________________
>> _______
>> UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org
>> Platinum Sponsor:rmisoluti...@verizon.net 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:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
>
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
