1

I have an Oracle 11g XE set up in Moscow, Russia, and therefore on MSK time zone. When a client in the same timezone stores a DATE variable from Java with, say time set to 12:00, the time is changed to 08:00 when I read it back. The Oracle data type is DATE (no TZ) and there is no timezone handling in my Java client.

The output of the following queries to check timezone settings in Oracle are:

select dbtimezone, sessiontimezone from dual;
DBTIMEZONE = +00:00
SESSIONTIMEZONE = Europe/Moscow  (which I guess is +03:00)

So the sessiontimezone is probably the culprit, but why does it affect non _TZ columns and what is the best way to solve it?

  • How are you getting the value to store in the date column? Are you calling sysdate? Or is the application tier generating the value? Inside the database, time zones don't matter with a date column so my guess would be that the middle tier is generating the value and the conversion is happening in the process of writing data to the database. – Justin Cave Apr 20 '16 at 20:46
  • @JustinCave The value is a Java.Util.Date that is converted to a java.sql.Timestamp to preserve the time (I think that is the reason) and then stored via java.sql.PreparedStatement.setTimestamp(..). – larslars Apr 20 '16 at 20:56
  • 1
    it seems that database time zone is set UTC (+00:00). Session timezone (client machine) is Moscow; when you store value in java, it actually goes as 12 (+3:00) which is equal to 9(or 8 in case of daylight savings) . The culprit is not Oracle, but jdbc driver. Unfortunately when it reads date back , it doesn't do reverse, but rather simply returns what is stored in db. You can check actual value stored in database with to_char(date_field, 'yyyy-mm-dd hh24:mi:ss') – a1ex07 Apr 20 '16 at 23:42
  • @a1ex07: Does that mean I should set the database time zone to Moscow as well? The DB server is on Moscow timezone (OS timezone) already, so I had assumed Oracle would comply to that. Or are you suggesting that I look for a Java-based solution? Thanks!! – larslars Apr 21 '16 at 7:57
1

DBTIMEZONE is different than host machine timezone, and it needs to be set up separately (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions039.htm) .

The first step is to change DBTIMEZONE to Moscow so it matches server settings which always makes sense. As a side effect, you will have your current issues "solved" because now db timezone also matches application server timezone .

However, I'd not recommend to stop at this point unless you can always run Oracle and java application[s] with Moscow timezone settings. It may be feasible in some cases, but it's not very robust solution. It easily turns to nightmare when you have to deal with switching to daylight saving time .

I'd rather change application to always store datetime in UTC , and perform all necessary conversion on application side before displaying date to the end user. Another option you can try is replacing DATE columns with one of Oracle timezone aware types (TIMESTAMP WITH LOCAL TIMEZONE or TIMESTAMP WITH TIMEZONE).

  • Sorry but changing DBTIMEZONE to Moscow did not solve my current issues. I used alter database set time_zone 'Europe/Moscow and restarted XE. The select dbtimezone from dual command now returns 'Europe/Moscow', but selecting a data field stored as 12:00 now returns 08:00 from my Java client but 10:00 from sqlplus running on the server. Not sure I understand that, but it is consistent with non-_TZ columns not being affected by DBTIMEZONE. I guess I will have to deal with it in the client, but we have never had issues during daylight saving before. So it is a tad mysterious. – larslars Apr 22 '16 at 12:14
  • What does Calendar.getInstance().getTimeZone() output on your application machine ? If app timezone matches db timezone, try checking session timezone from application . – a1ex07 Apr 22 '16 at 17:29
0

The solution turned out to be to adjust the timezone on the DB server, clients and the entire cluster to UTC+1 (Europe/Oslo). This is the same as the cluster that has run for years without problems. However, I'm not completely sure why the "+1" does not cause the same problems as the "+3" in MSK.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.