Oracle – Why Does Oracle Change Date Fields?

oracleoracle-11gtimezone

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?

Best Answer

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

Related Question