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 <[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"
