Problem with Date in Oracle using JDBC

Question

I've the following scenario: Two computer: First computer running under Windows Vista, second computer running under Linux, connecting both to Oracle 10g. Oracle 10g is running in the second computer.

I've have done one test program in Java that conecting to Oracle using ojdbc14.jar

This test program is only connecting to database, retrieving some data from a particular table and printing a value of date field.

When running the program test in the first machine, I've date like '10/01/1987' When running the program test in the first second, I've date like '09/30/1987'

When I using plsqldeveloper to show this data, the date field looks like '10/01/1987'

Somebody can explain why?

Thanks in advance

Answer 1

It looks like a timezone issue to me - one machine is set to, e.g., US time, and the other to GMT or UTC? Hence the dates are one day apart, but presumably have the same UTC / long value.

Are you using Timestamp as the column type in Oracle? e.g.,;

...
"created" timestamp(6) not null default systimestamp enable,
...

Then using java.sql.Timestamp? e.g.:

...
Timestamp created = rs.getTimestamp("created");
...

Answer 2

I think that maybe Oracle stores the date as a GMT value. What Oracle is doing is correct, but when reading the value from the Oracle database, your client needs to adjust to the GMT date. It appears "plsqldeveloper" is showing you the GMT-7 adjusted time when querying from the Windows box and the raw GMT when querying on the Linux box. I'd say the Linux box (or plsqldeveloper" on the Linux box) needs additional configuration for GMT adjustment.

By the way, if it actually is a GMT problem then it MUST be also storing the time of day BECAUSE that is the only way that a GMT-7 adjustment could appear to be 1 whole day off. So, I am curious what the "time of day" stamp is on your dates. Also, when you login to the Oracle admin tool and look at the date in there, how different is it? (If you dont have Oracle you can download the light/express edition and its easy to setup)

Answer 3

Be sure to also check the NLS_DATE_FORMAT setting.

This could be configured, or defaulted, differently between your two computers.

See this URL for more examples: http://www.dba-oracle.com/t_nls_date_format_sysdate.htm