0

If you run this query in toad against an oracle DB:

select 
to_char(TRUNC(sysdate, 'DAY'),'DAY') start_of_the_week
from dual;

Depending on the locale servers settings, you will get:

Sunday --EN, USA
Monday -- DE, GERMANY

This makes perfect sense and is all right.

If you do the exact same thing in Java through the OJDBC driver like this:

//....
Statement stm = JDBCConn.createStatement();
stm.execute("select to_char(TRUNC(sysdate, 'DAY'),'DAY') start_of_the_week from dual");
stm.getResultSet().next();
System.out.println(stm.getResultSet().getString("start_of_the_week"));

You will get Sunday or Monday back, but this is depending on your client local settings provided to the JVM you are running this statement. This means the answer you get back is no longer depending on the server local settings but on your client ones.

The fix is really easy, add

Locale.setDefault(new Locale("EN", "US", "WIN")); //or German etc..

Now to my actual question. Why would anyone think this is a good idea?!? Is there a situation why this makes sense? Is this a feature, because in my opinion this is a bug, or at least very bad design/concept.

Regards, Colin

Colin
  • 101
  • 8
  • 1
    I think it makes sense that the driver uses the *client's* preferences - after all that's where the data is displayed. What do you get if you use SQL*Plus? If I remember correctly that uses the client's locale as well. –  Aug 13 '14 at 16:58
  • I think I tried with SQL Plus and it's the server time. Yeah maybe it is a question of what you expect it to be. If work with a technology like JavaScript I would never expect something to be server based, if I work with DB technologies I would never expect something to be client based. But that could just be me... – Colin Aug 26 '14 at 13:44

1 Answers1

0

this is supposedly jdbc driver feature

be careful Locale.setDefault - sets jvm default, that would affect other parts of your application

with oracle you also have to be careful when handling timestamp kind of columns and how to map them to java

See http://tonyhasler.wordpress.com/2010/09/04/tonys-tirade-against-timestamp-with-time-zone/

Daylight saving time and time zone best practices

In the Oracle JDBC driver, what happens to the time zone when you write a Java date to a TIMESTAMP column?

Community
  • 1
  • 1
Kalpesh Soni
  • 6,879
  • 2
  • 56
  • 59
  • Actually, [`Locale.setDefault`](http://docs.oracle.com/javase/8/docs/api/java/util/Locale.html#setDefault(java.util.Locale.Category,%20java.util.Locale)) affects not only other parts of your app, but affects every piece of Java code running within that JVM. Every other app, every Servlet, every library. – Basil Bourque Aug 13 '14 at 22:42
  • I am aware of Locale.setDefault affecting the whole JVM. It's exactly what I want. Still don't see a pro argument for the behaviour of the OJDBC driver. Actually even the opposite, in the links you posted with best practices they say the following: 1. Never trust client datetime. It may very well be incorrect. 2. Do not compare client datetimes with server datetimes. Another two arguments why this behaviour is just very missleading or in my opinion just wrong... – Colin Aug 14 '14 at 07:19