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"
