I’ve recently changed my columns from “TIMESTAMP” to “TIMESTAMP WITH TIMEZONE”.
Normally I would have a model class that would have a “LocalDateTime” but now I’ve changed to include timezone I use an “OffsetDateTime” to match the type used in the generated code by JOOQ.

I’m using the latest JOOQ and H2. H2 is in Postgres mode.

Upon trying to save into the USER_MODEL table I get an error.

Caused by: io.r2dbc.h2.H2DatabaseExceptionFactory$H2R2dbcDataException: Data conversion error converting "CHARACTER LARGE OBJECT to TIMESTAMP WITH TIME ZONE"; SQL statement:
select "id" from final table (merge into "user_model" using (select cast($1 as varchar(1000000000)) "email", cast($2 as varchar(1000000000)) "password", cast($3 as varchar(1000000000)) "role", cast($4 as varchar(1000000000)) "tier", cast($5 as varchar(1000000000)) "first_name", cast($6 as varchar(1000000000)) "last_name", cast($7 as boolean) "enabled", cast($8 as timestamp(9) with time zone) "created_at", cast($9 as timestamp(9) with time zone) "updated_at", cast($10 as smallint) "total_retries") "t" on "user_model"."id" = cast($11 as uuid) when matched then update set "user_model"."email" = $12, "user_model"."password" = $13, "user_model"."role" = $14, "user_model"."tier" = $15, "user_model"."first_name" = $16, "user_model"."last_name" = $17, "user_model"."enabled" = $18, "user_model"."created_at" = $19, "user_model"."updated_at" = $20, "user_model"."total_retries" = $21 when not matched then insert ("email", "password", "role", "tier", "first_name", "last_name", "enabled", "created_at", "updated_at", "total_retries") values ("t"."email", "t"."password", "t"."role", "t"."tier", "t"."first_name", "t"."last_name", "t"."enabled", "t"."created_at", "t"."updated_at", "t"."total_retries")) "user_model" [22018-230]
    at io.r2dbc.h2.H2DatabaseExceptionFactory.convert(H2DatabaseExceptionFactory.java:60) ~[r2dbc-h2-1.0.0.RELEASE.jar:1.0.0.RELEASE]
    at io.r2dbc.h2.client.SessionClient.query(SessionClient.java:138) ~[r2dbc-h2-1.0.0.RELEASE.jar:1.0.0.RELEASE]
    at io.r2dbc.h2.H2Statement.lambda$execute$2(H2Statement.java:145) ~[r2dbc-h2-1.0.0.RELEASE.jar:1.0.0.RELEASE]
    at reactor.core.publisher.FluxMapFuseable$MapFuseableSubscriber.onNext(FluxMapFuseable.java:113) ~[reactor-core-3.6.8.jar:3.6.8]
    ... 71 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "CHARACTER LARGE OBJECT to TIMESTAMP WITH TIME ZONE"; SQL statement:

Here is the SQL to generate my table:

CREATE TABLE user_model (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY ,
  email TEXT NOT NULL CONSTRAINT user_model_email CHECK (LENGTH(email) <= 320 ),
  password TEXT NOT NULL CONSTRAINT user_model_password CHECK (LENGTH(password) <= 50),
  role TEXT NOT NULL CONSTRAINT user_model_role CHECK (LENGTH(role) <= 50),
  tier TEXT NOT NULL CONSTRAINT user_model_tier CHECK (LENGTH(tier) <= 50),
  first_name TEXT NOT NULL CONSTRAINT user_model_first_name CHECK (LENGTH(first_name) <= 50),
  last_name TEXT NOT NULL CONSTRAINT user_model_last_name CHECK (LENGTH(last_name) <= 50),
  enabled BOOLEAN NOT NULL,
  created_at TIMESTAMP(9) WITH TIME ZONE NOT NULL,
  updated_at TIMESTAMP(9) WITH TIME ZONE NOT NULL,
  total_retries SMALLINT NOT NULL CONSTRAINT user_model_total_retries CHECK (total_retries >= 0)
);

UserModel

data class UserModel(
    override var id: UUID? = null,
    val email: String?,
    var password: String,
    var role: RoleDto,
    var firstName: String?,
    var lastName: String?,
    var enabled: Boolean,
    var createdAt: OffsetDateTime?,
    var updatedAt: OffsetDateTime?,
    var totalRetries: Long = 0,
    var tier: UserTier
) : BaseModel(id)

The code I’m running is this. It takes the user model, maps it to a record and inserts it.

        val record = ctx.newRecord(table, model)
        val id: UUID = ctx.insertInto(table)
            .set(record)
            .onConflict()
            .doUpdate()
            .set(record)
            .returning(idField)
            .awaitFirst()
            .getValue(idField)!!

Here are the values for the model and record printed out.

UserModel(id=null, [email protected], password=abcd=, role=USER, firstName=Michael, lastName=Name, enabled=false, createdAt=2024-08-10T19:49:33.731172500+01:00, updatedAt=2024-08-10T19:49:33.731172500+01:00, totalRetries=0, tier=FREE)
+------+----------------------------+---------------------------------------------+-----+-----+----------+---------+-------+------------------------------------+------------------------------------+-------------+
|ID    |EMAIL                       |PASSWORD                                     |ROLE |TIER |FIRST_NAME|LAST_NAME|ENABLED|CREATED_AT                          |UPDATED_AT                          |TOTAL_RETRIES|
+------+----------------------------+---------------------------------------------+-----+-----+----------+---------+-------+------------------------------------+------------------------------------+-------------+
|{null}|*[email protected]|*yODttTr/abcd=|*USER|*FREE|*Michael  |*Name|*false |*2024-08-10T19:49:33.731172500+01:00|*2024-08-10T19:49:33.731172500+01:00|           *0|
+------+----------------------------+---------------------------------------------+-----+-----+----------+---------+-------+------------------------------------+------------------------------------+-------------+

What could be causing this issue? It worked fine when just using TIMESTAMP and LocalDateTime but I can’t get this working with TIMESTAMP WITH TIMEZONE and OffsetDateTime.