I stand corrected. Great to know this function exist.
On Mon, Aug 24, 2009 at 7:06 PM, 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: arslist@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 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"
