Re: Trying to get the UTC time out of ORACLE

"Bill Lucas" <Homebrew42_at_hotmail.com> wrote in message news:wS57a.16430$jR3.8239240_at_news1.news.adelphia.net...
> I thought this would be rather simple... At first anyway.
>
> I was originally going to use this function
>
> FUNCTION GETUTC RETURN TIMESTAMP AS
>
> tsUTC TIMESTAMP;
>
> BEGIN
>
> tsUTC := SYS_EXTRACT_UTC(SYSTIMESTAMP);
>
> RETURN(tsUTC);
>
> END GETUTC;
>
> From the docs that looked like it would work, and when I checked
> SYSTIMESTAMPs information for Today I Got
> 26-FEB-03 10.54.38.968000000 AM -05:00 <-- Correct offset FOR EST
>
> When I set it to after DST would be in effect I got this...
>
> 06-APR-03 03.00.27.515000000 AM -04:00 <-- Correct Offset for EDT
>
> Ok so far so good...
>
> However the Result of SYS_EXTRACT_UTC(SYSTIMESTAMP) was an hour off for
EDT
> as if it was still using -05:00 time zone information.
>
> So When I Called SYS_EXTRACT_UTC(SYSTIMESTAMP) from today I got
>
> 02/26/2003 04:06:14.14 PM (Which is right for a base time of 11:06:14.14
AM
> EST)
>
> Then I moved the time forward to June 26th and got this as my result
>
> 06/26/2003 04:07:19.19 PM (Which is off by an hour it should be 3:07:19.19
> PM with a base time of 11:07:19.19 AM EDT)
>
> At first I thought maybe some of the info was being pulled from the
Windoze
> registry and the registry wasn't being updated so I let the time roll over
> DST and saw the clock change. Alas no change in the results of
> SYS_EXTRACT_UTC(SYSTIMESTAMP); as it was an hour off for EDT
>
> I even tried this
>
> SYS_EXTRACT_UTC(TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH:MI:SS.SS AM
> TZH:TZM')));
>
> No Dice even though
> TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH:MI:SS.SS AM TZH:TZM')
> Showed the time zone as -4:00 WHEN in EDT and -5:00 in EST the
> SYS_EXTRACT_UTC still gave the wrong result for EDT off by 5 hours instead
> of 4
>
> I am sure I am missing something here and it can't be this hard to get UTC
> time from an ORACLE Database. So can anyone tell me what it is I am
> missing?
>
> We are developing on Oracle 9i on Windoze 2000 AS SP3
>
>
> Thanks for any help,
>
> Regards,
> Bill
>

Well I spent a little more timne playing with the problem and found this solution...

  FUNCTION GETUTC RETURN TIMESTAMP AS        tsUTC TIMESTAMP;

  BEGIN        --tsUTC := SYS_EXTRACT_UTC(SYSTIMESTAMP); -- This didn't work with Daylight Saving Time

       tsUTC := SYSTIMESTAMP - (TO_NUMBER(SUBSTR(SYSTIMESTAMP, - 6, 3)) / 24);

    RETURN(tsUTC);

  END GETUTC; It is a little more convoluted but it works and in my inital unit testing it returned the corect UTC time irregardless of time zone and DST, however, I would still appreciate comments on this code. I am trying to find the best way to do this not _A_ way of doing it. We will use this function extensivly in our application and I would like it to perform as well as possible. I am also interesteds in learning more about Oracle and if there is a built in or preferred way of doing what I am trying I would like to learn about it. I would also like to hear about any flaws in the logic of this code. I am sure I could not have accounted for all cases and I know I don't know what could happen or how this could fail.

Would a to_char with an explicit mask be wise in this case, or will the format of SYSTIMESTAMP not change and just make the code harder to read?

Any and all comments welcome.

Regards,
Bill Received on Wed Feb 26 2003 - 12:20:28 CST