Re: Daylight Savings Time Translation Error with Oracle 8i

From: David Sanabria <david.sanabria_at_morte_spam.thehartford.mortespam.com>
Date: Tue, 12 Apr 2005 20:46:08 GMT
Message-ID: <425C338A.5070703@morte_spam.thehartford.mortespam.com>

Hi Sybrand,

Thanks for your response. I always appreciate help.

My urgency is this: Since DST began (U.S. Eastern Daylight Savings Time, GMT-4), my reports that convert from epoch time and should show GMT-4 are actually displaying GMT-5. When I turn off the Windows (no Windows cracks, please) Auto DST setting in the Date/Time control panel, my dates are displayed using the correct GMT-4 (even though Windows now thinks it's GMT-5).

To give some context:

Our database is storing data created by BMC's Action Request System (a.k.a. Remedy). It's native date format is Epoch time, and times are not translated until the date is displayed to the user by way of the Remedy client (a very smart choice).

I chose to do my reporting against this data, but have chosen to convert the dates on the server so that reports that I create (as views) presnet the same data regardless of client (MS Excel, Crystal Reports, Perl, etc). All of my views present the data in DATE format so that I don't have to write conversion code on any client.

Our problems started after DST began and I have not been able to find the root cause.

Two questions:
1) How can I verify the GMT offset that Solaris is using 2) Once #1 is known, what is the recommended course to address this issue (assumed to be: Change to correct TZ or offset).

Many thanks!

Dave
developer turned junior DBA ;)

Sybrand Bakker wrote:
> On Tue, 12 Apr 2005 15:15:00 GMT, David Sanabria
> <david.sanabria_at_morte_spam.thehartford.mortespam.com> 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 

>
>
> What is your pressing need you are doing this?
> Oracle doesn't have it's own date at all, and just derives it from the
> system date.
> That said, on *Nix this would imply you have your TZ variable
> incorrectly set, and on Winblows you simply aren't using DST.
>
> Also please do not cross and multipost.
> Your audience won't be bigger, as the frequent responders monitor all
> three groups.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Tue Apr 12 2005 - 15:46:08 CDT