Vert.x PostgreSQL Client 4.x: Standardizing TIMESTAMPTZ to UTC or Regional Zones

Answer

Store all instants as UTC by mapping PostgreSQL TIMESTAMPTZ to java.time.OffsetDateTime with an offset of zero (+00:00). Configure the Vert.x PostgreSQL client to force the database session time zone to UTC, and use a single Jackson ObjectMapper with the JavaTimeModule for JSON serialization.

Likely explanation / assumptions

If the session time zone is left to the JVM default or varies per connection, the same TIMESTAMPTZ value may be read back with a different offset, causing shifts in tests, deployments, and JSON payloads. Setting the session explicitly removes that variability.

Steps to implement

1. When creating the PgPool, add an init script or connection property that sets the session time zone to UTC, e.g.:

   PgPoolOptions opts = new PgPoolOptions()
       .setPort(5432)
       .setHost("localhost")
       .setDatabase("mydb")
       .setUser("user")
       .setPassword("secret")
       .addInitScript("SET TIME ZONE UTC;"); // or use connection property "TIMEZONE=UTC"
   PgPool client = PgPool.pool(vertx, opts);

2. Verify the setting (optional diagnostic): after obtaining a connection, run SHOW TIMEZONE; and confirm it returns UTC. If you are using a Vert.x PostgreSQL client version older than 4.2.0, please confirm your version, as earlier releases may mis-map TIMESTAMPTZ to LocalDateTime.

3. Configure Jackson once for the whole application:

   ObjectMapper mapper = new ObjectMapper()
       .registerModule(new JavaTimeModule())   // handles OffsetDateTime, Instant, etc.
       .disable(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS); // ISO‑8601 strings

   Use this mapper in all Vert.x verticles and HTTP endpoints (e.g., via a shared utility or dependency injection).

4. When persisting, store OffsetDateTime directly; the driver will send it as a TIMESTAMPTZ with UTC offset.

5. For display, convert at the view layer: ZonedDateTime zdt = offsetDateTime.atZoneSameInstant(ZoneId.of("America/New_York")); Never persist the converted value.