Hi SQLDelight team. Postgres types `timestamp` and...

# squarelibraries

[David B]

Hi SQLDelight team. Postgres types timestamp and timestampz aren’t currently modelled in SQLDelight as java.time.Instant value in Java / Kotlin. They’re modelled as LocalDateTime and OffsetDateTime instead: https://sqldelight.github.io/sqldelight/2.0.2/jvm_postgresql/types/

The naming of these column types in Postgres is somewhat confusing. The “with timezone” makes it sound like it’s storing some timezone data in your column. It isn’t. The “with timezone” only affects how a String is converted to and from a Timestamp when you’re using a SQL Client. You’re telling Postgres “I want you to interpret my string in the context of the Postgres server’s timezone” or “I want you to display these timestamps with respect to whatever timezone I’ve set in the Postgres server”. These String-based concerns have no relevance when you’re reading and writing a value from a Java object that’s backed by an unambiguous Long milliseconds-since-Epoch UTC value.

Anyway, to cut a long story short, I think that the choice of Instant is the most appropriate way of reading and writing these timestamp (milliseconds since Epoch) values. I’d be interested to hear your thoughts, and whether this discussion has already been held. Thanks.

[David B]

My comments apply most to timestamp, which I think is definitely a perfect fit for Instant. I don’t think LocalDateTime is a good fit.

For timestampz, we an argue that OffsetDateTime type tells the caller about the server timezone set on Postgres. Most of the time, the caller doesn’t want to know this. Allowing the use of OffsetDateTime leads to the confusing situation where the user saves a NewYork timestamp into the database and then fetches back a London timestamp when doing a SELECT because the server’s timezone happens to be set to London. Instant removes the expectation of the timezone being saved and therefore removes the surprise.

[griffio]

Which driver are you using? Jdbc or R2dbc

Jdbc Postgresql doesn’t yet support Instant see https://jdbc.postgresql.org/documentation/query/#using-java-8-date-and-time-classes
R2dbc does https://github.com/pgjdbc/r2dbc-postgresql?tab=readme-ov-file#data-type-mapping

Currently, OffsetDateTime is used in PostgreSqlType as both drivers support it and doesn’t need any internal conversion

The developer can always choose the type that makes sense for their model using SqlDelight type adapters e.g ``OffsetDateTime`` to Instant
See here for a similar issue https://github.com/sqldelight/sqldelight/pull/5629#issuecomment-2640351787 (edited)

[David B]

Thanks very much for the info. I’ve made the adapter solution work, as you suggested, by having AS Instant in my SQL file. I’ll take it up with the Postgres JDBC team 🙂 . Looks like this is the relevant issue from 2017: https://github.com/pgjdbc/pgjdbc/issues/833

I suppose that SQLDelight could take the position that Instant is better and, therefore, expose Instant and perform the conversion from OffsetDateTime without the user being aware that there was ever an OffsetDateTime. The adapter solution works well, so not required.