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.
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 asDBTIMEZONE(recommended and usuallyUTC). The timestamp is always and only displayed in the current user sessionSESSIONTIMEZONE. 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 ZONEyou don't have to care about any settings of your client, the time is always shown as local time. The time is stored inDBTIMEZONE, 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 forSYS_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 mean2005-10-30 02:00:00+03:00or2005-10-30 02:00:00+04:00Take this example:
Have a look at TIMESTAMP WITH TIME ZONE Data Type
Note, time zone
+04:00or+03:00is not equal toEurope/Moscow. TimezoneEurope/Moscowconsiders daylight-saving-times (when it was still used in Russia about 10 years ago) but+04:00/+03:00does 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.Dateand methodsetDaterefers toDATEdata type in Oracle. As already stated, better usejava.sql.TimestampandsetTimestamp.The
DATEdata type does not support any time zone information. If you try to insert aDATEvalue into aTIMESTAMP WITH [LOCAL] TIME ZONEcolumn then Oracle actually does