How to use SQL timestamp with java Instant in jOOQ?

Question

I want to use Instant type to put it to MySQL database (timestamp field). Unfortunately, when using POJO and Record#from(), jOOQ doesn't let me do that for some reason. I have the following to my gradle configuration:

forcedTypes {
    forcedType {
        name = "Instant"
        types = "timestamp"
    }
}

The code is being generated correctly, but doesn't work and gives me errors in runtime:

 Data truncation: Incorrect datetime value: '2021-01-16 05:01:25.457+00:00' for column `test`.`messages`.`time_sent` at row 1

I tried to add my own converter and binder, but they don't seem to work if name is set in gradle config, as a warning appears during build. But without name I can't get jOOQ to generate Instant for timestamp field.

Is there a way to use Instant with SQL timestamp when using jOOQ's POJO?

Answer 1

MySQL's timestamp data type is a bit, well, idiotic.

It stores time as seconds since the epoch (jan 1st 1970, UTC, midnight), in a 32-bit integer. This has a few issues:

• There is no room for milliseconds. Your Instant has .457 milliseconds and this needs to be stripped out, which is why JOOQ is refusing to do this; that's destroying data. I assume you don't care about those millis, but JOOQ doesn't know that. You can try to strip the millis out of the instant, if you must, JOOQ would presumably allow saving an Instant, if that Instant has the property that it the epochmillis that it wraps around is divisible by 1000 (has no millis part). Except, that was annoying, so at some point, eventhough it's stored as 32-bit seconds-since-epoch, the data type now also contains, separately, a fractional second, from 0 to 999999. Either you have an old version of MySQL or the underlying table structure that doesn't allow this, or JOOQ isn't aware that MySQL does at least support millis.
• At 2038-01-19 03:14:07 UTC, your app explodes. That's it. That's the last timestamp representable by a MySQL TIMESTAMP object. We're less than 18 years removed from this. Therefore this datatype is effectively unusable, and you should use something else. (This may not sound believable to you. Peruse MySQL8's user manual §11.2.2 if you need some convincing, straight from the source). Java's core instant storage system doesn't suffer from the dreaded Y2K38, as java uses millis-since-epoch in 64-bit; we got a few billion years to go before that runs out of numbers.
• Note that the printed message is a bit misleading. Instants are stored as milliseconds since epoch and do not have a timezone, that +00.00 in the printout suggests that it does. It doesn't - and thus the fact that mysql's TIMESTAMP type also doesn't isn't the problem.

Solutions

The best solution, by far, is to use a database engine that isn't broken like this. For example, try postgres.

A distant second solution is to peruse JOOQ issue #9665 where @lucaseder (core contributor of JOOQ; he's the genius doing the magic over there :P) says it doesn't work yet, but there's some code there you may be able to use.

Note that if you actually care about zones, this becomes a lot more complicated. There are 3 different ways to represent time; these are distinct and cannot be converted to each other without additional info and caveats; most tools incorrectly silently convert them anyway, and pain ensues when this happens. Thus, you may want to think about which of the 3 kinds of time you have here:

• solarflares time: A moment time something happened or will happen, such as a solarflare. If some political entity decides to change timezone, it has no effect. The 'time until event occurs' / 'time since event occurred' goes up by 1 second every second no matter what happens with timezones. In java terms, java.time.Instant.
• appointment time: I call my barber in Amsterdam and I have an appointment on Jan 5th, 2023, 14:00. You'd think this is like solarflares time, but, no. If the dutch parliament slams the gavel and adopts a law that the Netherlands will no longer observe daylight savings and will remain in summertime, then the moment that gavel comes down, the # of seconds until my appointment goes up by an hour (is it it down by an hour?). This is not exotic at all - see EU Directive 2000/84/EC - it is, in fact, likely. Solarflares time should not shift like this, and appointment time does. Best represented as year+month+day+hour+minute+second+millis+a full zone (which is Europe/Amsterdam, not silly useless stuff like +0800 or PST). In java terms, ZonedDateTime.
• Alarmclock time: Just years, months, day, hour, minute, second. That's it - it doesn't represent anything particular but just the concept. If I set my alarm to wake me up at 08 in the morning and I take a trip across the pacific, the time until my alarm goes off should change drastically as I move timezones. In java terms, LocalDateTime and friends.

I'm assuming you have a solarflares time (for example, to track 'user X change their password at this time'). This answer assumes you meant that. If you did not, my advice would change a bit. Mostly that whole 'do not use mysql' thing would become even stronger: What you really want is the datatype TIMESTAMP WITH TIME ZONE such as postgres has.