Re: Daylight Savings Time Translation Error with Oracle 8i

From: David Sanabria <david.sanabria_at_morte_spam.thehartford.mortespam.com>
Date: Thu, 14 Apr 2005 19:12:30 GMT
Message-ID: <ygz7e.3483$716.3451@newssvr19.news.prodigy.com>

Here's an update on my findings:

Last year, we were using Remedy AR Server v4.5. We upgraded to AR 5.1.x at the end of 2004. We didn't have this problem last year.

Our database is running on an older version of Solaris (SunOS) and it reports the date and TZ variables as:

     $ date
     Thu Apr 14 14:39:09 EDT 2005

     $ echo $TZ
     US/Eastern

It would seem to me that our server is set to the right time zone *however* read on for the plot twist.

When I turn of the Auto DST feature in the Windows Date/Time Control Panel, dates in April that _were_ displaying as (correct time) - 1h now display correctly!

As best I can tell, Windows is applying a a timezone translation (at the lower ODBC and OLEDB levels) to reduce time by an hour because it is trying to convert from GMT-4 (on the server) to GMT-5 (on the Windows workstation). Turning off the Auto DST "feature" also turns off this translation.

I have attached my date conversion code (inline below) for the benefit of all Remedy users. If you can see anything within my code that might be a cause of our problems, please let me know. (otherwise, enjoy the code).

Regards,
Dave

(NOTE: Code is at the end of the message after the quote)

David Sanabria wrote:
> I am having problems with getting dates out of Oracle since DST began.
> My problem is that I am converting an EPOCH date from seconds to the
> native Oracle DATE on the server and once I pull the data out through
> ODBC or ADO, the dates are getting set to GMT -5 rather than GMT - 4.
>
> I have tried the following:
>
> 1. Used Perl DBD::Oracle to retreive data: Success, dates not affected
> 2. ADO via VBScript 5.5: Failure. Times in April affset by -5
> 3. ADO via Crystal Reports 9 with latest service pack: Failure,
> dates in april offset by -5
> 4. ODBC via Crystal Reports: Failure. times ofset by -5
>
> I am using Oracle 8.1.7.4. Oracle client version 9.2 and 10.1. ORacle
> ADO and MS ADO. Oracle ADO connects but times are wrong, MS ADO fails
> becuase it doesn't like CLOBS.
>
> I would apreciate any thoughts and suggestions.

[SNIP] BEGIN Code_Sample

CREATE OR REPLACE FUNCTION DST_BEGIN( nInYear IN NUMBER := -1) RETURN DATE
IS

     datResult DATE;
     nYear     NUMBER(4);

BEGIN /*
Daylight Saving Time begins for most of the United States at 2 a.m. on the first
Sunday of April. Time reverts to standard time at 2 a.m. on the last Sunday of October.
In the U.S., each time zone switches at a different time. */

     IF nInYear = -1 THEN --use current year
         nYear := TO_NUMBER( TO_CHAR( SYSDATE, 'YYYY'));
     ELSE -- use given year
         nYear := nInYear;
     END IF;

     datResult := (NEXT_DAY( '31-MAR-' || nYear , 'Sunday') + (1/12));


     RETURN datResult;

END DST_BEGIN; /

CREATE OR REPLACE FUNCTION DST_END( nInYear IN NUMBER := -1) RETURN DATE
IS

     datResult DATE;
     nYear     NUMBER(4);

     IF nInYear = -1 THEN --use current year
         nYear := TO_NUMBER( TO_CHAR( SYSDATE, 'YYYY'));
     ELSE -- use given year
         nYear := nInYear;
     END IF;

     datResult := (NEXT_DAY( '24-OCT-' || nYear , 'Sunday') + (1/12));


     RETURN datResult;

END DST_END; /
CREATE OR REPLACE FUNCTION TO_UTC_DATE( nARTimestamp IN NUMBER ) RETURN DATE IS

BASE_DATE CONSTANT DATE := TO_DATE( '01-Jan-1970', 'DD-MON-YYYY'); SECS_PER_DAY CONSTANT NUMBER := 86400; datTheDate DATE;

BEGIN

     datTheDate := BASE_DATE + ( nARTimestamp / SECS_PER_DAY );
     RETURN datTheDate;

EXCEPTION
WHEN OTHERS THEN
RETURN NULL; END TO_UTC_DATE; /

CREATE OR REPLACE FUNCTION GMT_OFFSET_SECS( datInDate IN DATE ) RETURN NUMBER
IS

     c_DAYLIGHT_OFFSET      CONSTANT PLS_INTEGER := 14400;  -- UTC -5
     c_STANDARD_OFFSET      CONSTANT PLS_INTEGER := 18000;  -- UTC -4

     nYear    NUMBER(4);
     nResult  PLS_INTEGER;

BEGIN nYear := TO_NUMBER( TO_CHAR( SYSDATE, 'YYYY'));

      IF datInDate BETWEEN dst_begin( nYear ) AND dst_end( nYear ) THEN
          nResult := c_DAYLIGHT_OFFSET;
      ELSE
          nResult := c_STANDARD_OFFSET;
      END IF;

      RETURN nResult;

END GMT_OFFSET_SECS; /

CREATE OR REPLACE FUNCTION To_Ar_Date( datTheDate IN DATE ) RETURN NUMBER IS

BASE_DATE CONSTANT DATE := TO_DATE( '01-Jan-1970', 'dd-Mon-YYYY' );

GMT_OFFSET CONSTANT NUMBER := 18000; SECS_PER_DAY CONSTANT NUMBER := 86400;

    nResult        NUMBER;
    nGMTOffset     PLS_INTEGER;

BEGIN nGMTOffset := Gmt_Offset_Secs( datTheDate );

     nResult := (( datTheDate - BASE_DATE ) * SECS_PER_DAY ) + nGMTOffset;
     nResult := ROUND( nResult, 0 );
     RETURN nResult;

EXCEPTION
WHEN OTHERS THEN
RETURN NULL; END To_Ar_Date;

CREATE OR REPLACE FUNCTION To_System_Date( nARTimestamp IN NUMBER ) RETURN DATE
IS

     nExceptionID      NUMBER(9);
     nExceptionResult  PLS_INTEGER;
     xLN               PLS_INTEGER := 0;

SECS_PER_DAY CONSTANT NUMBER := 86400;

    datTheDate     DATE;
    nGMTOffset     PLS_INTEGER;

BEGIN xLN:=10; IF nARTimestamp IS NULL THEN

xLN:=20;        RETURN NULL;
              END IF;

xLN:=30; datTheDate := To_Utc_Date( nARTimestamp ); xLN:=40; nGMTOffset := Gmt_Offset_Secs( datTheDate );

xLN:=50; datTheDate := datTheDate - ( nGMTOffset / SECS_PER_DAY ); xLN:=60; RETURN datTheDate;

EXCEPTION

     WHEN OTHERS THEN
         nExceptionResult :=
         Exception_Log_Pkg.LOG_EXCEPTION( 
Exception_Log_Pkg.c_MSG_TYPE_DATA_DESC
                                          ,'FUNCTION'
                                          ,'To_System_Date'
                                          ,NULL
                                          ,xLN
                                          ,SQLCODE
                                          ,SQLERRM
                                          ,NULL
                                          ,0
                                          ,nExceptionID );
      RETURN NULL;

END To_System_Date;

END code_sample Received on Thu Apr 14 2005 - 14:12:30 CDT