General
cancel
Showing results for 
Search instead for 
Did you mean: 

oracle SYSDATE is different from HPUX date

Rick Garland
Honored Contributor

oracle SYSDATE is different from HPUX date

Hi all:

Got Oracle 8.1.6 on HPUX 11.00 and the time from the Oracle banner is some 15 min off what the HPUX system date says. I am using ntp so I am sync'd to TZ=EST5EDT.

Most everything I see says the TZ variable, but the TZ=EST5EDT.

What gives?

Many thanks!
12 REPLIES
Yogeeraj_1
Honored Contributor

Re: oracle SYSDATE is different from HPUX date

hi,

Seems like a timezone settings problem.

In 8i and before, does not have timezone support.

In 9i there is a datatype "timestamp with timezone". The timestamp can store the date, time (down to 9 decimal places with seconds), and the TIMEZONE. The database "understands" timezones so if you get the difference between 1pm PST and 4pm EST it understands that it is in fact the same time. And so on.

In short, 9i supports and is timezone aware. 8i and before -- not so.

Below a piece of code to determine what Oracle is seeing on the server it is running. Please run it and post your output.

yd@mydb.MU> create or replace and compile java source
2 named "demo"
3 as
4 import java.net.*;
5 import java.io.*;
6 import java.util.*;
7 import java.text.*;
8 public class demo
9 {
10 static public void java_get_timezone( String[] p_timezone )
11 {
12 Date d = new Date();
13 DateFormat df2 = new SimpleDateFormat( "z" );
14 df2.setTimeZone( TimeZone.getDefault() );
15 p_timezone[0] = df2.format(d);
16 }
17* }
18 /

Operation 160 succeeded.

Elapsed: 00:00:14.60
yd@mydb.MU> create or replace package tz
2 as
3 procedure get_timezone( p_timezone out varchar2 )
4 as language java
5 name 'demo.java_get_timezone( java.lang.String[] )';
6 function get_tz return varchar2;
7* end;

Package created.

Elapsed: 00:00:01.61
yd@mydb.MU> create or replace package body tz
2 as
3 g_tz varchar2(25);
4 function get_tz return varchar2
5 as
6 begin
7 if g_tz is null then get_timezone(g_tz); end if;
8 return g_tz;
9 end;
10* end;

Package body created.

Elapsed: 00:00:00.03
yd@mydb.MU> select tz.get_tz from dual;

GET_TZ
_______
GMT

Elapsed: 00:00:02.09

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
eran maor
Honored Contributor

Re: oracle SYSDATE is different from HPUX date

Hi Rick

i know that you all ready check the issue of the timezone but one more thing to check .

check the var TZ of the user oracle .
most of the time the time is sync and dfine in the /etc/TIMEZONE correctry but the var in the .profile of the user oracle is not set rigth .
love computers
Rick Garland
Honored Contributor

Re: oracle SYSDATE is different from HPUX date

The TZ as oracle see it is EST5EDT

The TZ variable for the oracle acct correct and from the command line (logged in as oracle) the date will return the proper value.

Going to create a tar for Oracle to explain...


Many thanks
Brian Crabtree
Honored Contributor

Re: oracle SYSDATE is different from HPUX date

You will also want to check what the TZ variable for the owner of the listener process as well (if it is different than oracle). If you are connecting remotely, it is the listener process that defines the TZ variable.

Brian
R. Allan Hicks
Trusted Contributor

Re: oracle SYSDATE is different from HPUX date

I'd be curious to see how this comes out.

One of the things that used to get us on timezone was when the listener was started in one TZ and the server was started in another. Connected via the listener or the bequeth connection would cause sysdate to be returned in the appropriate timezone.

Example:

TZ=EST5EDT

dbstart

TZ=GMT

lsnrctl start

sqlplus foo/foome@myserver

select sysdate from dual;

returns GMT time

sqlpus foo/foome

select sysdate from dual;

returns EST5EDT time

The part that throws me is the 15 minute difference. If the difference was an integer number of hours, I'd say timezone.

What timezone is 15 minutes different from another?

Good luck
"Only he who attempts the absurd is capable of achieving the impossible
T G Manikandan
Honored Contributor

Re: oracle SYSDATE is different from HPUX date

Found a document for VMS at metalink which is attached.

Please update once the solution is resolved.


Thanks
Rick Garland
Honored Contributor

Re: oracle SYSDATE is different from HPUX date

One thing I am noticing is that all of the suggestions are recommending TZ variable is some fashion.

I can see this being a definite solution to the issue if there is a time difference of some multiple of whole hours (1 hrs difference, 2 hrs difference, etc.). This situation has a time difference of 13 min. The OS time presents the correct time. The Oracle banner (and so forth) presents the time 13 min ahead.
A. Clay Stephenson
Acclaimed Contributor

Re: oracle SYSDATE is different from HPUX date

Yes Rick, that very observation was what caused me not to respond. I couldn't (and still can't) explain a 15 minute error. Everytime I launch SQLPLUS, the time agrees exactly with the system time. Yesterday, I nm'ed sqlplus and wrote the output to a file. You might want to try that and grep -i for "time". There are many references to time functions but I have to believe that they ultimately refer to the time() system call. The only things that I can suggest are 1) relink Oracle libraries and executables 2) contact Oracle support and tell them what you are seeing.
If it ain't broke, I can fix that.
R. Allan Hicks
Trusted Contributor

Re: oracle SYSDATE is different from HPUX date

I agree with why are we barking up the TZ tree. The point and/or question I was asking was:

Is the problem sensitive to the way you connect?

Seems like when I went through the listener, it used the time with respect to the listener's environment. When I used the bequeth connection, it used the server's environment.

I was wondering how you connected to sqlplus. Could it be that say using connection manager or names server or active directory causes you to use a different time source? Say the one on the connection manager's machine?

Just Wondering.
"Only he who attempts the absurd is capable of achieving the impossible
Rick Garland
Honored Contributor

Re: oracle SYSDATE is different from HPUX date

Many thanks for feedback.

The connection can be local or remote, using the listener or not using the listener - achieving the same troubling results.

Fornunately this is a tst system but it is still aggrevating.

Have opened a tar with oracle - lets see what they say and I will post here.

Again, thanks!
Yogeeraj_1
Honored Contributor

Re: oracle SYSDATE is different from HPUX date

hi,

this problem is indeed intriguing me!

can you confirm that the following statement is also giving you this 15 mins off?

select sysdate from dual;

can you post a copy+paste of the entire output?

thanks
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)

Re: oracle SYSDATE is different from HPUX date

Hi Rick Garland,

We were very pleased to see this problem has been tackled and although I understand that you are not obliged to answer, no misunderstanding here, I and my oracle colleague would be able to help a customer if you can reply with the oracle tar number that you opened in May. My email stuart.hill@sap.com. Thank you for your help.

Stuart Hill
Bernhard Baumann