Exposed's exposed-java-time (and exposed-kotlin-datetime) module has an issue: In PostgreSQL it uses TIMESTAMP WITHOUT TIME ZONE and always saves the timestamp using the system's current time zone (ZoneId.systemDefault()).

So, if you are running your application in two different time zones, the inserted result of Instant.now() will be different, even if the code was executed at the same nanosecond!

I got bit by this bug in Loritta, where the legacy application was using the America/Sao_Paulo time zone while the newer slash commands web server was using UTC. This caused inconsistencies in the database where transactions inserted by the legacy application were older than transactions inserted by the web server!

While you can workaround the issue by using TimeZone.setDefault(TimeZone.getTimeZone("UTC")) in your code, this isn't a great solution. Besides, it is recommended that you use TIMESTAMP WITH TIME ZONE, even if you are using a java.time.Instant!

Thankfully Exposed is very extendable, allowing you to support features that it doesn't support out of the box! So I submitted my TIMESTAMP WITH TIME ZONE implementation to my ExposedPowerUtils repository, with other nifty Exposed utilities and extensions.

object ActionLog : LongIdTable() {
    // Instead of using timestamp, use "timestampWithTimeZone"
    // This returns a "Instant"
    val timestamp = timestampWithTimeZone("timestamp")
}

Check out the ExposedPowerUtils repository, include the postgres-java-time in your project, and have fun! Or if you prefer, just copy the JavaTimestampWithTimeZoneColumnType file to your project.

Keep in mind that Exposed won't change the column type automatically if the table is already created, you can change it by ALTER TABLE sonhostransactionslog ALTER COLUMN timestamp TYPE TIMESTAMP WITH TIME ZONE;. In my experience, if your current TIMESTAMP WITHOUT TIME ZONE are in UTC, you shouldn't have any issues when migrating to a TIMESTAMP WITH TIME ZONE!

---

---