Java and Oracle timestamp with time zone

Question

I'm trying to save two dates in Oracle with hibernate. both dates have the same timestamps in the moscow time zone: 2005-10-30T02:00+03:00[Europe/Moscow] and 2005-10-30T02:00+04:00[Europe/Moscow] ("Sun Oct 30 02:00:00 MSK 2005" and "Sun Oct 30 02:00:00 MSD 2005"). the dates are separated in time by one hour and are associated with the transition to winter/summer time.

I created the table in Oracle:

create table TMP
(
    ID    LONG,
    TS    TIMESTAMP,
    TSLTZ TIMESTAMP WITH LOCAL TIME ZONE,
    TSTZ  TIMESTAMP WITH TIME ZONE
);

and entity in my module:

@Entity
@Table(name = "tmp")
public class DateTimeOracle {

    private Long id;
    private ZonedDateTime ts;
    private ZonedDateTime tsltz;
    private ZonedDateTime tstz;

    @Id
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public ZonedDateTime getTs() {
        return ts;
    }

    public ZonedDateTime setTs(ZonedDateTime ts) {
        this.ts = ts;
    }

    public ZonedDateTime getTsltz() {
        return tsltz;
    }

    public ZonedDateTime setTsltz(ZonedDateTime tsltz) {
        this.tsltz = tsltz;
    }

    public ZonedDateTime getTstz() {
        return tstz;
    }

    public ZonedDateTime setTstz1(ZonedDateTime tstz) {
        this.tstz = tstz;
    }
}

In entity, all fields are initialized by a single date. After saving, both dates in Oracle have the same values and look like:

ts = 2005-10-30 02:00:00.000000

TSLTZ = 2005-10-29 23:00:00.000000

TSTZ = 2005-10-30 02:00:00.000000 +04:00

Why does oracle keep the same value for different dates (including offset +04:00)? Is there any way to fix this?

P.S. Postgres stores the date correctly. One with an offset +03:00, the other with an offset +04:00 (2005-10-29 23:00:00.000000 and 2005-10-29 22:00:00.000000, respectively).

Update

This is how i create the dates:

Date dt2 = new Date(1130623200000L); //2005-10-29 23:00:00 +04:00
Date dt3 = new Date(1130626800000L); //2005-10-29 23:00:00 +03:00
ZonedDateTime zdt2 = ZonedDateTime.ofInstant(dt2.toInstant(), ZoneId.systemDefault()); // My zone is MSK
ZonedDateTime zdt3 = ZonedDateTime.ofInstant(dt3.toInstant(), ZoneId.systemDefault()); // My zone is MSK
OffsetDateTime odt2 = zdt2.toOffsetDateTime();
OffsetDateTime odt3 = zdt3.toOffsetDateTime();

If I not use Hibernate and use jdbc directly, the situation doesn't change.

Connection conn = DriverManager.getConnection("<oracle_url>",
                        "<username>", "<password>");
PreparedStatement pstmt = conn.prepareStatement("insert into tmp (id, TSTZ1, TSTZ2) values (200, ?, ?)", Statement.RETURN_GENERATED_KEYS);
pstmt.setDate(1, new java.sql.Date(dt2.getTime()));
pstmt.setDate(2, new java.sql.Date(dt3.getTime()));
int z1 = pstmt.executeUpdate();
pstmt.close();
conn.close();

Update2

If I save OffsetDateTime via the jdbc driver in timestamp with local timezone or in timestamp with time zone, then everything is fine.

PreparedStatement pstmt = conn.prepareStatement("insert into tmp (TSLTZ1, TSLTZ2, TSTZ1, TSTZ2) values (?, ?, ?, ?)");    
pstmt.setObject(1, odt2);
pstmt.setObject(2, odt3);
pstmt.setObject(3, odt2);
pstmt.setObject(4, odt3);

I see in DB:

2005-10-29 22:00:00.000000  2005-10-29 23:00:00.000000  2005-10-30 02:00:00.000000 +04:00   2005-10-30 02:00:00.000000 +03:00

But if I save ZonedDateTime, then the values in timestamp with local timezone are correct, but in timestamp with time zone are incorrect.

PreparedStatement pstmt = conn.prepareStatement("insert into tmp (TSLTZ1, TSLTZ2, TSTZ1, TSTZ2) values (?, ?, ?, ?)");    
pstmt.setObject(1, zdt2);
pstmt.setObject(2, zdt3);
pstmt.setObject(3, zdt2);
pstmt.setObject(4, zdt3);

In DB I see:

2005-10-29 22:00:00.000000  2005-10-29 23:00:00.000000  2005-10-30 02:00:00.000000 +04:00   2005-10-30 02:00:00.000000 +04:00

The last two values are incorrect.

Answer 1

Some explanations about the Oracle TIMESTAMP data types:

• TIMESTAMP: Does not store any timezone information. If you enter a timestamp with time zone then the time zone information is simply truncated and lost.

• TIMESTAMP WITH TIME ZONE: Stores the timestamp with time zone information (i.e. either as named region or as UTC-Offset) as you insert the timestamp into database.

• TIMESTAMP WITH LOCAL TIME ZONE: Timestamp is stored as DBTIMEZONE (recommended and usually UTC). The timestamp is always and only displayed in the current user session SESSIONTIMEZONE. Thus it does not display any time zone information, because by definition this is always your local time zone.

Which one should I use?

It depends on your requirements - of course.

With TIMESTAMP WITH LOCAL TIME ZONE you don't have to care about any settings of your client, the time is always shown as local time. The time is stored in DBTIMEZONE, thus you lose the original inserted time zone.

Be aware, when you create an index on TIMESTAMP WITH TIME ZONE. It is not possible to create an index directly on such column. Instead Oracle creates a virtual column for SYS_EXTRACT_UTC(TSTZ) and creates the index on this virtual column. You should pay attention to this when you develop your queries.

Update

You have quite a special situation. When you insert TIMESTAMP '2005-10-30 02:00:00 Europe/Moscow' then this time is ambiguous, it could mean 2005-10-30 02:00:00+03:00 or 2005-10-30 02:00:00+04:00

Take this example:

SELECT TO_CHAR(TIMESTAMP '2005-10-30 00:00:00 Europe/Moscow' + LEVEL * INTERVAL '1' HOUR, 
    'YYYY-MM-DD hh24:mi:ss TZH:TZM TZD tzr') AS ts
FROM dual
CONNECT BY LEVEL <= 4;

+--------------------------------------------+
|TS                                          |
+--------------------------------------------+
|2005-10-30 01:00:00 +04:00 MSD Europe/Moscow|
|2005-10-30 02:00:00 +04:00 MSD Europe/Moscow|
|2005-10-30 02:00:00 +03:00 MSK Europe/Moscow|
|2005-10-30 03:00:00 +03:00 MSK Europe/Moscow|
+--------------------------------------------+

Have a look at TIMESTAMP WITH TIME ZONE Data Type

To eliminate the ambiguity of boundary cases when the time switches from Standard Time to Daylight Saving Time, use both the TZR format element and the corresponding TZD format element. The TZD format element is an abbreviation of the time zone region with Daylight Saving Time information included. Examples are PST for U. S. Pacific Standard Time and PDT for U. S. Pacific Daylight Time. The following specification ensures that a Daylight Saving Time value is returned:

TIMESTAMP '1999-10-29 01:30:00 America/Los_Angeles PDT'

If you do not add the TZD format element, and the datetime value is ambiguous, then Oracle Database returns an error if you have the ERROR_ON_OVERLAP_TIME session parameter set to TRUE. If ERROR_ON_OVERLAP_TIME is set to FALSE (the default value), then Oracle Database interprets the ambiguous datetime as Standard Time.

Note, time zone +04:00 or +03:00 is not equal to Europe/Moscow. Timezone Europe/Moscow considers daylight-saving-times (when it was still used in Russia about 10 years ago) but +04:00/+03:00 does not.

Sorry, I never used hibernate, so I don't know how this framework handles such data. I am not familiar with Java either. Maybe the Daylight Saving Time information is not supported.

I can just guess, class java.sql.Date and method setDate refers to DATE data type in Oracle. As already stated, better use java.sql.Timestampand setTimestamp.

The DATE data type does not support any time zone information. If you try to insert a DATE value into a TIMESTAMP WITH [LOCAL] TIME ZONE column then Oracle actually does

FROM_TZ(CAST(<your DATE value> AS TIMESTAMP), SESSIONTIMEZONE)