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.


TIA,
Dave


N. David Sanabria
Specialist, DA
The Hartford, Infrastructure Solutions Department
Online Services Information Delivery
W: 1.860.547.7786 * M: 1.860.916.3489
@: david.sanabria (AT) thehartford (DOT) morte.spam.com
Don't be afraid to take a big step if one is indicated; you can't cross
a chasm in two small jumps. David Lloyd George

On Tue, 12 Apr 2005 15:15:00 GMT, David Sanabria
<david.sanabria (AT) morte_spam (DOT) thehartford.mortespam.com> wrote:

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

Hi David,

Using the number you retrieve for the epoch time, you can can derive
the date, and then convert it from GMT to the time zone you want. You
can use Oracle's new_time function, provided it supports your time
zone.

http://download-west.oracle.com/docs...tion.htm#78068

For example, let's say you retrieve a row where the epoch date is
1000000. Converting it to days gives you:

SQL> select 1000000/(24*60*60) from dual;

1000000/(24*60*60)
------------------
11.5740741

So, a little over 11 days past the 1st of January:

SQL> select to_date('JAN-01-1970','MON-DD-YYYY')+(1000000/(24*60*60))
from dual;


TO_DATE('
---------
12-JAN-70

1* select
to_char(to_date('JAN-01-1970','MON-DD-YYYY')+(1000000/(24*60*60)),'M
ON-DD-YYYY HH:MI:SS AM') from dual
SQL> /

TO_CHAR(TO_DATE('JAN-01
-----------------------
JAN-12-1970 01:46:40 PM

My timezone is currently EDT (GMT-4). So, using the new_time function,
I can retrieve the date in a particular time zone and then convert it
to a character representation, such as MON-DD-YYYY HH:MI:SS AM:
SQL> select
to_char(new_time(to_date('JAN-01-1970','MON-DD-YYYY')+(1000000/(24*60*60)),'GMT','EDT'),'MON-DD-YYYY
HH:MI:SS AM') from dual;

TO_CHAR(NEW_TIME(TO_DAT
-----------------------
JAN-12-1970 09:46:40 AM

Here's what happens if I try EST (-5) and PST (-8):

SQL> select
to_char(new_time(to_date('JAN-01-1970','MON-DD-YYYY')+(1000000/(24*6
0*60)),'GMT','EST'),'MON-DD-YYYY HH:MI:SS AM') from dual;

TO_CHAR(NEW_TIME(TO_DAT
-----------------------
JAN-12-1970 08:46:40 AM

1* select
to_char(new_time(to_date('JAN-01-1970','MON-DD-YYYY')+(1000000/(24*6
0*60)),'GMT','PST'),'MON-DD-YYYY HH:MI:SS AM') from dual
SQL> /

TO_CHAR(NEW_TIME(TO_DAT
-----------------------
JAN-12-1970 05:46:40 AM

If the function doesn't include your time zone, you can still get the
date in the format you want by adding the quotient of your time zone's
gmt offset and 24. So, let's say I want to display a date in the
Australian Eastern Standard Time format (GMT +10):

1* select
to_char(to_date('JAN-01-1970','MON-DD-YYYY')+(1000000/(24*60*60))+(1
0/24),'MON-DD-YYYY HH:MI:SS AM') from dual
SQL> /

TO_CHAR(TO_DATE('JAN-01
-----------------------
JAN-12-1970 11:46:40 PM

Is this what you're looking for?

Regards,
Arun

Arun,

Thanks for the response.

I believe that I have narrowed this issue down to the TZ environ
variable. If not there specifically, then Time Zone is _defintely_ a factor.

The conversions that you show are spot on for multi-TZ conversion, but
my good fortune is that I only have to provide reports in a single
timezone for consumers within that timezone. My Misfortune is that
daylight savings time have snarled up all of my plans.

Your code is very good, but not what I need. I really do appreciate your
giving time to answer my question!

Regards,
Dave



Arun Mathur wrote:

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 20:46:08 GMT, David Sanabria
<david.sanabria (AT) morte_spam (DOT) thehartford.mortespam.com> wrote:

1) TZ is ordinarily set in /etc/profile
IIRC, it is always set, so you could right now issue echo $TZ in a
client session even when it is not set in the client's .profile
2) The problem is usually the default timezones do not always have the
correct offsets. I usually just modify the /etc/profile
The correct offsets are, IIRC (to be verified with man timezone or man
TZ) 3:5-10:5, 5 meaning 'The last Sunday'


--
Sybrand Bakker, Senior Oracle DBA

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:
[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);

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( '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

On Thu, 14 Apr 2005 19:12:30 GMT, David Sanabria
<david.sanabria (AT) morte_spam (DOT) thehartford.mortespam.com> wrote:

Well, it still think it can be accomplished by modifying the TZ
environment variable,as I indicated before. Your problem is obviously
*Nix and Mickeysoft aren't in agreement about the start and end of
DST. I doubt whether it really has anything to do with Remedy, though
I admit Remedy is a crap product, which should be thrown away at the
first occasion. In our case we were forced to switch the database to
noarchivelog, because there was no way to cope with the volume of
redolog generated.




--
Sybrand Bakker, Senior Oracle DBA

Hello David,

This is exactly what I am trying to do and call the crystal report through an oracle view. Now I am completely new to oracle and was wondering if you could help me. Your to_system_date function is giving me an error(I am using Oracle SQL developer) under Oracle 9.2. Any ideas what is wrong? I have uploaded the file. Also, once this works, how do I call this function in a view.
What I am trying to do is say
Select Status,
Functionthatyourwrote(Arrival_time) as ArrivalTimeConverted
Functionthatyourwrote(Modified_time) as ModifyTimeConverted
FROM
Hpd_helpdesk

Your urgent help is greatly appreciated David or anyone else for this matter.

Thanks.

David:

Be sure the TZ is set and exported in all environments on the solaris
box, including the one with the listener, the one with the database,
and IIRC Remedy has its own application server. How long have these
processes been up? Is anything running under csh?

jg
--
@home.com is bogus.
http://www.crank.net/day.html