Support timestamp with timezone/offset

[beikov]

We figured out that most databases do not support what JDBC and the SQL standard had in mind for TIMESTAMP_WITH_TIMEZONE which is, that the timezone/offset is stored. Only the following databases implement this, AFAIK:

• H2
• MySQL
• Oracle

This means that for every other database, we would need to have a dedicated column to store the timezone/offset, which requires a composite user type.

[gavinking]

We figured out that most databases do not support what JDBC and the SQL standard had in mind for TIMESTAMP_WITH_TIMEZONE

So I think there's essentially no problem here. Let me describe the current state of the H6 branch:

1. For databases with no support for timestamp with time zone (e.g. DB2), we fall back to timestamp. The JDBC drivers for these databases do usually (assuming I recall correctly) allow you to pass in an OffsetDateTime or ZonedDateTime and I guess they do whatever conversion to they think is appropriate. On balance, I think this is the most correct behavior, based on the fact that I don't think Hibernate should have an opinion on what to do with an OffsetDateTime persisted to a timestamp column. That's between the user and their database to fight about. (IMO, the user should probably be using LocalDateTime here.)
2. For databases which support timestamp with time zone by converting the given datetime to UTC (e.g. Postgres), we let the database do the conversion. This seems quite clearly correct to me, and an improvement over the dodginess of stuff like hibernate.jdbc.time_zone in H5.
3. For databases which really do support the full semantics of timestamp with timezone (only H2 and Oracle, AFAIK, not sure why you mention MySQL, perhaps that's new??), we actually let you persist the time zone or offset. Once again, this seems to me the clearly correct thing to do. I don't think we should go throwing away the zone or offset on Oracle.

It's been suggested that the current behavior in H5 is perfectly fine, but just I don't think that's right:

• The mere existence of hibernate.jdbc.time_zone suggests that it doesn't always do the right thing.
• It doesn't let Oracle users take advantage of Oracle's correct support for timestamp with time zone.
• It (brokenly) treats ZonedDateTimes as OffsetDateTimes, implicitly supposing that the user has explicitly used a ZonedDateTime for no good reason.
• It does, AFAIK, completely unnecessary in-memory timezone conversions on databases like Postgres and I strongly believe that we're better off leaving those conversions as a task for the JDBC driver and database. I just don't think Hibernate should get involved in that.

Note that none of the above discussion applies to LocalDateTime, which continues to map to the timestamp type, and IIRC continues to work as today.

(Caveat: it's possible I have some of the above details slightly wrong because it's a long time since I worked on this stuff.)

[gavinking]

To be clear, users who care about cross-database portability should certainly not be using ZonedDateTime or OffsetDateTime.

[gavinking]

To give even a bit more context: the really major difference between H5 and H6 in this area is that:

• In H5, an OffsetDateTime or ZonedDateTime always gets converted to a java.sql.Timestamp before getting sent to JDBC, and we always read Timestamp from JDBC. (Prior to JDBC 4.2 this was the right thing to do.)
• In H6, we pass an OffsetDateTime or ZonedDateTime directly to the JDBC driver. (This is supposed to be supported since 4.2.)

I think it would be a mistake to revert to the old legacy behavior. That was I guess the right thing to do before JDBC drivers had support for OffsetDateTime and ZonedDateTime. It's simply not the right thing to do now.

[sebersole]

I spoke with @beikov about this a bit this morning. He suggested adding a @TimeZoneStorage annotation accepting a "stragey". I think this is a great solution as it addresses all of our points of view

I can see that we may all disagree about the interpretation of AUTO, but hopefully we can all agree on the approach. We could also even introduce a setting to control the interpretation of AUTO (i.e. hibernate.orm.time.zone.default_storage=...)

We came up with the set of strategies as:

@interface TimeZoneStorage {
    enum Strategy {
        /** Per dialect support, possibly normalizing either by the database (PGSQL, e.g.) */
        DIALECT,
        /** Error if dialect does not provide "pass through" support for "with timezone" types */
        NATIVE,
        /** To UTC (5.x behavior) */
        NORMALIZE
        /** stores the timezone in a separate column; works in conjunction with {@link TimeZoneColumn} and {@link CompositeUserType}, both of which are optional */
        COLUMN,
        /** a compound selection - first use DIALECT if the Dialect supports it otherise NORMALIZE (5.x behavior) */
        PICK,
        /** The default - equivalent to, imo, either PICK, DIALECT or NORMALIZE */
        AUTO
    }
}

Not tied to the name PICK, just all I can think of atm

[yrodiere]

That seems nice. IMO we absolutely need the setting, though: I don't see myself configuring this explicitly on every single date-time property.

Regarding the timezone column, I'd suggest to configure it through an attribute of @TimeZoneStorage? Unless there really is a need for a specific annotation.

@interface TimeZoneStorage {
    Column timeZoneColumn() default @Column();

    enum Strategy {
        /** Per dialect support, possibly normalizing either by the database (PGSQL, e.g.) */
        DIALECT,
        /** Error if dialect does not provide "pass through" support for "with timezone" types */
        NATIVE,
        /** To UTC (5.x behavior) */
        NORMALIZE
        /** stores the timezone in a separate column; works in conjunction with {@link TimeZoneColumn} and {@link CompositeUserType}, both of which are optional */
        COLUMN,
        /** a compound selection - first use DIALECT if the Dialect supports it otherise NORMALIZE (5.x behavior) */
        PICK,
        /** The default - equivalent to, imo, either PICK, DIALECT or NORMALIZE */
        AUTO
    }
}

[sebersole]

Personally I dislike "optional" attributes that are dependent on other attribute values if that makes sense.

E.g. it allows stuff like

@TimeZoneStorage( column = ..., strategy = NORMALIZE )
ZonedDateTime ...;

[yrodiere]

E.g. it allows stuff like

Well, so does the alternative:

@TimeZoneStorage( NORMALIZE )
@TimeZoneColumn( ... )
ZonedDateTime ...;

At least when everything is within the same annotation, there is some amount of guidance: if I use TimeZoneStorage, I can have a look at the attributes and see related configuration.

I will grant you that using only top-level annotations is more in line with the JPA way, though. For better or worse.

[dreab8]

I think the hibernate.orm.time.zone.default_storage setting is necessary too.

[gavenkoa]

SQL Server JDBC drivers support TIMESTAMP_WITH_TIMEZONE:

• https://docs.microsoft.com/en-us/sql/connect/jdbc/jdbc-4-2-compliance-for-the-jdbc-driver?view=sql-server-ver15

New Java classes in Java 8:
LocalDate/LocalTime/LocalDateTime
OffsetTime/OffsetDateTime

New JDBC types:
TIME_WITH_TIMEZONE
TIMESTAMP_WITH_TIMEZONE
REF_CURSOR

https://stackoverflow.com/questions/51273979/localdatetime-and-sql-server-jdbc-4-2-driver mentions v7.1.1 as "fixing" version. Current is 9.4.1.

I can set parameter:

statement.setObject(1, OffsetDateTime.parse("2021-02-03T00:00:00-02:00"), JDBCType.TIMESTAMP_WITH_TIMEZONE);

but when I retrieve parameter of SQL type datetimeoffset:

final Object obj = rs.getObject(1);

it is of type microsoft.sql.DateTimeOffset which has methods java.time.OffsetDateTime getOffsetDateTime() & java.sql.Timestamp getTimestamp()

[gavinking]

So, we're having to revisit this unfortunately.

What has occurred to me today is to think of what are the possible strategies if I want really portable, consistent behavior across databases. And I think there are just four five:

1. Always normalize to UTC in Java.
2. Always store the timezone in a separate column.
3. Let the database normalize to UTC where that's supported (Postgres and others), and fall back to normalizing to UTC in Java.
4. Use "fully correct" timestamp with time zone where that's supported (Ora, SQL Server, I believe), and fall back to using a separate column.
5. Use timestamp with time zone wherever it's supported (Ora, SQL Server, Postgres, etc), and fall back to normalizing to UTC in Java.

Options 2 and 4 always preserve the time zone info. Options 1 and 3 always normalize to UTC and throw away the timezone. Option 5 may or may not preserve the time zone info, but it at least conserves the "instant" part of the datetime.

I don't think we should use a separate column unless the user explicitly requests it. It's just a "weird" thing to do. Rules out 2 + 4.

Also, I don't think that if the user has explicitly used an OffsetTimeZone or ZonedDateTime in their model, that we should, by default, make no attempt to preserve the timezone info. Rules out 1 + 3.

That leaves 5 as a reasonably default behavior. (Under the assumption of "portable semantics".)

[gavinking]

(The above is just speculation, by the way. Just trying to get a handle on the range of options.)

[sebersole]

I agree with (5). That's been my preference for a long time. I also dislike the separate column approach, especially as a default option.

[gavinking]

I agree with (5). That's been my preference for a long time.

Right, so back in September last year I wrote:

For databases with no support for timestamp with time zone (e.g. DB2), we fall back to timestamp. The JDBC drivers for these databases do usually (assuming I recall correctly) allow you to pass in an OffsetDateTime or ZonedDateTime and I guess they do whatever conversion to they think is appropriate. On balance, I think this is the most correct behavior, based on the fact that I don't think Hibernate should have an opinion on what to do with an OffsetDateTime persisted to a timestamp column. That's between the user and their database to fight about. (IMO, the user should probably be using LocalDateTime here.)

And I guess that after reflecting on that, maybe I don't believe it anymore. We've recently gone to great lengths to abstract other sorts of differences between databases, so it makes sense that we would also do it here.

That's been my preference for a long time.

I never knew that. In fact it had never even occurred to me as a possibility until last night.

I also dislike the separate column approach, especially as a default option.

I don't completely hate it. I think it's a fine thing to have as an option. But I don't think it should be the default on any db.

[gavinking]

See my summary of this matter here: https://hibernate.atlassian.net/browse/HHH-15820?focusedCommentId=110421