Hi Fred,
thanks for this valueable input.
Can you please enlight me how to connect this with my:
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';
Basically I need to run a query that will show the correct time of the
outage, correct i.e. in the right time zone and DST corrected .
Thanks,
Mark
On 24 Aug., 18:06, "Grooms, Frederick W" <frederick.w.gro...@xo.com>
wrote:
> Oracle 9i (and higher) functions for Date Time conversion
>
> Change 'US/Central' to the time zone of your database. The Oracle functions
> will automatically perform the correct DST corrections (as long as you have
> the Oracle DST patches installed in the database)
>
> CREATE OR REPLACE FUNCTION FROM_EPOCH (secs IN NUMBER)
> RETURN DATE
> IS
> lDate DATE;
> BEGIN
> lDate := (From_TZ((TO_DATE('01/01/1970', 'dd/mm/yyyy')+(secs/86400)),'GMT')
> AT TIME ZONE 'US/Central');
> RETURN lDate;
> END From_Epoch;
> /
>
> CREATE OR REPLACE FUNCTION TO_EPOCH (lDate IN DATE)
> RETURN NUMBER
> IS
> Seconds NUMBER;
> dte1 TIMESTAMP;
> dte2 TIMESTAMP;
> BEGIN
> dte1 := new_time(lDate, to_char( from_tz(cast (lDate as timestamp),
> 'US/Central'),'TZD'),'GMT');
> dte2 := TO_TIMESTAMP_TZ('01/01/1970 '|| TZ_Offset('Greenwich'),
> 'MM/DD/YYYY TZH:TZM');
> Seconds := (((dte1-0) - (dte2-0)) * 86400);
> RETURN Seconds;
> END To_Epoch;
> /
>
> Fred
>
> -----Original Message-----
> From: Action Request System discussion list(ARSList)
> [mailto:arsl...@arslist.org] On Behalf Of Frank Caruso
> Sent: Monday, August 24, 2009 10:32 AM
> To: arsl...@arslist.org
> Subject: Re: Date Time Conversion Issue in Remedy
>
> 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
>
> >> -----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
>
> >> --
> >> "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
>
> >> --_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 atwww.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"
