One more question. How to figure out, what from what time zone is the
my timestamp?
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"
