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.
