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"
