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?

4
  • 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. Apr 20, 2016 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, 2016 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, 2016 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, 2016 at 7:57

2 Answers 2

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).

2
  • 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, 2016 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, 2016 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 and acknowledge you have read our privacy policy.

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