TL;DR Instant.now() or ZonedDateTime.now() or LocalDateTime.now() in Java 8 can only give you time resolution up to milliseconds. In Java 9+, however, you can get resolution up to microseconds (sometimes nanoseconds), depending on the underlying native clock. Also, Oracle’s TIMESTAMP type defaults to a precision of 6. In addition, older versions of MyBatis had a bug with truncating timestamp values. Interesting bits of trivia, with the unlimited potential for confusion.

How I learned of this, you might ask. (No, I do not spend my leisure time reading and re-reading the Java docs..) I was working on something which required me to insert a timestamp in a table in an Oracle database, and then to query that table using the same timestamp. Here is what the code was doing (simplified):

• Create an object containing a field with a timestamp (generated from Java, for example with LocalDateTime.now()).
• Insert a new row with the details of the object from above, in table_1.
• Do some stuff.
• Insert a row in table_2, but, at insertion time, look up something in table_1 using the timestamp from before.

The Oracle column which took the timestamp had a TIMESTAMP type (no explicit precision).

Now, I was running this code locally and it was working fine, exactly as I had intended it to. Then, a strange thing happened - a colleague pinged me and told me that it didn’t work for him. (Ahh, the age old problem - it works on my machine but not on yours!) We started debugging it and wanted to see what exactly was breaking. We could see that step 2 from above was working fine… The row in table_1 was inserted without issue… Step 3 was behaving normally, and it wasn’t doing anything nefarious… The problem must have been with step 4 then!

But what was it?!

We found the issue when we looked at the debug logs of MyBatis. When my colleague ran the code, the timestamp it generated was something like 2020-12-07T16:58:29.241788 but, after inserting it in the DB, the column had 07-DEC-20 16:58:29.242000 PM. We had lost the microsecond precision!

This meant that, later, when the code reached step 4, it would use 2020-12-07T16:58:29.241788 to lookup table_1 but it would not match the truncated value and, hence, the INSERT would not be done.

Why did it work for me?

Because the timestamp I generated looked like 2020-12-07T16:57:37.981. So, in the database, we would have 07-DEC-20 16:57:37.98100000 PM which would be a perfect match and the INSERT would complete.

What was the difference between my setup and my colleague’s setup?

I was running the code on Java 8, while he was running it on Java 11. Apparently, in Java 9+ they changed the way java.time.Clock works:

In JDK 8 the implementation of the clock returned was based on System.currentTimeMillis(), and thus has only a millisecond resolution. In JDK 9, the implementation is based on the underlying native clock that System.currentTimeMillis() is using, providing the maximum resolution available from that clock.

On most systems this can be microseconds, or sometimes even tenth of microseconds.

That’s cool, but why was the value getting truncated?

Good question! When you think about it, a precision of 6 (which is what the timestamp column had by default) fits microsecond values. The truncation was done by MyBatis. I don’t really know why, but I can tell you that upgrading MyBatis from version 3.4.6 to 3.5.6 fixed the issue. I assume at some point they fixed a bug with their type handlers, but I haven’t had time to rummage through the changelogs to figure out when that had happened.

But wait, there’s more! Now that this issue was fixed, there were still failures.

But why?!

As noted earlier, the .now() methods can give you precision up to the maximum provided by the underlying native clock. This meant that calling Instant.now() could give something like 2020-12-07T16:58:29.2417889.

Did you notice it? There’s an extra 9 in there, giving us one more digit of precision! However, this is 1 more than Oracle’s column’s TIMESTAMP type allowed (by default), so what was getting inserted was actually 07-DEC-20 16.58.29.24178900 PM. Again, this did not match the original value when the lookup was done later.

Fixing this was not really interesting. I used another way to query table_1 which did not rely on the timestamp. I also could have changed the precision on the column in Oracle, or could have forced a precision in the Java code, for example with Instant.now().truncatedto(MICROS).

Remember kids - don’t mix versions! And if you do - make sure you know what the implications are. Also, make sure you understand what your tools are doing when you’re not paying attention - defaults can offer interesting surprises, to say the least…

I am looking forward (in all seriousness!) to people telling me how stupid I am and that I should have been doing <insert clever thing here> instead. Any wisdom you have is welcome!