SqlTypes.TIMESTAMP_UTC to model UTC stored timestamp for java.time.Instant

[beikov]

Create a SqlTypes constant for TIMESTAMP_UTC which will map to timestamptz on PostgreSQL/CockroachDB and timestamp on MySQL/MariaDB which store no zone offset, but instead normalizes to UTC. This type can be used as recommended JDBC type for java.time.Instant and for the TimeZoneStorageType.NORMALIZE strategy.

Currently, when binding/extracting a value for this type, we go through a java.sql.Timestamp instance which drivers seem to normalize to the system timezone. We could improve the situation here by requesting normalization to UTC. Another advantage of this type is that we can register a JdbcType implementation for JDBC drivers that support it, which avoids wrapping Instant into a java.sql.Timestamp and pass the Instant directly instead.

I didn't research much yet, but maybe other databases also support some kind of DDL type that also does UTC normalization, which this new constant would allow us to make use of.

[gavinking]

That sounds like a very good suggestion to me.

[gavinking]

Indeed, I think historically we got ourselves into some messes by trying to limit ourselves in terms to the given JDBC type codes.

[gavinking]

I didn't research much yet, but maybe other databases also support some kind of DDL type that also does UTC normalization, which this new constant would allow us to make use of.

IIRC there are. They interpret timestamp with time zone this way. Actually I believe Postgres has that as an alternative syntax to timestamptz.

[gavinking]

https://www.postgresql.org/docs/14/datatype-datetime.html

The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension.

Yeah, that's what I thought. So we should use timestamp with time zone, not timestamptz.