Both java.sql.Timestamp and java.time.Instant have nanosecond resolution since their introduction.

JDK-8068730 has nothing to do with it, in only changes resolution of methods like Instant.now() that was always limited to milliseconds in Java 8. They now return results with higher resolution if available. Not necessary with the maximum possible resolution of the operating system, for example, on my Linux system nanosecond resolution is available, but Java 9 uses only microseconds, that is probably enough, because Instant.now() is slow so any better resolution in not necessary, accuracy will not be better. H2 does not use these methods at all, its CURRENT_TIMESTAMP() resolution is still limited to milliseconds, this is not changed on Java 9+, but it usually does not matter, it's not easy to perform 1000+ transactions per second in real-world usage.

H2 strictly follows the SQL standard for TIMESTAMP data type since 1.4.197. TIMESTAMP means TIMESTAMP(6) as required (older versions incorrectly handle it as TIMESTAMP(9). You can always set resolution that suits your needs by specifying fractional seconds precision that should be in range from 0 (resolution is limited to seconds) to 9 (nanoseconds).
http://www.h2database.com/html/datatypes.html#timestamp_type
Oracle also handles resolution of this data type in exactly the same way. Default resolution of 6 (microseconds) should be enough for your needs.

I do not understand what timestamps are compared in your tests and why you have a problem with it. There are too many things that can potentially be done wrong. Perhaps a simple test case that shows your problem is needed.

please re-open if you have a test case

Sure, @grandinj . I'll add a test case when I get time.

But @katzyn doesn't seemed to have followed my explanation, so let me explain a bit more.

Let's say I'm using Java 8 and I use Instant.now(). I get an Instant instance (say that five times fast), which I can print out using toString() (which uses the ISO 8601 representation). It might print: 2018-06-14T01:02:03.456Z.

Instant bar = Instant.now();
System.out.println();

Now let's say I have a foo table with a bar column created using bar TIMESTAMP DEFAULT SYSTIMESTAMP. Actually the default doesn't matter; I'm going to insert my instant explicitly. Remember that I'm using Oracle mode, in case it matters.

INSERT INTO foo(id, bar) VALUES (?, ?)

Using JDBC I plug in "foo-id" for the ROW ID, and bar for the bar timestamp. Here's how I insert the value from an Instant:

preparedStatement.setTimestamp(2, Timestamp.from(bar));

Then I do a SELECT and, still using JDBC, I query that same row (using the foo-id, for sake of example). I pull out the bar value into a bar2 value, like this:

Instant bar2 = resultSet.getTimestamp("bar").toInstant();

If I print out bar2 here, it has the same thing as bar. (Remember that I'm still using Java 8.) I can use a JUnit assert:

assertThat(bar, is(bar2));  //passes under Java 8

Now suppose I upgrade to Java 9/10 and go back in time and do all this over.

Instant bar = Instant.now();
System.out.println();

Now because Java 9/10 has microsecond precision, it prints out something different!

2018-06-14T01:02:03.456789Z

Nice! More precision is always better. So I stored it in H2 in Oracle mode, and then query it again—just like I did above. I can print out bar2:

2018-06-14T01:02:03.456Z

Oops, that's not the same! Sure enough, if I do a JUnit test of the two Instant instances for equality, the test fails.

assertThat(bar, is(bar2));  //fails under Java 9/10

I can get around this by truncating to milliseconds:

assertThat(bar.truncatedTo(MILLIS), is(bar2.truncatedTo(MILLIS)));

As I mentioned at the first of this ticket, this isn't a showstopper, but I do wonder why H2 isn't storing the microseconds.

Or maybe the problem is that Oracle's default precision for TIMESTAMP doesn't handle microseconds. I don't know; I haven't had a chance to explore it that far.

I can't reproduce this. I tried the latest released version of H2 (1.4.197) and the following code sample:

import java.sql.*;
import java.time.Instant;

public class TestTS {
	public static void main(String[] args) throws SQLException {
		try (Connection c = DriverManager.getConnection("jdbc:h2:mem:1;MODE=Oracle")) {
			Statement s = c.createStatement();
			s.execute("CREATE TABLE TEST(T TIMESTAMP NOT NULL)");
			PreparedStatement ps = c.prepareStatement("INSERT INTO TEST VALUES (?)");
			Instant i1 = Instant.now();
			ps.setTimestamp(1, Timestamp.from(i1));
			ps.executeUpdate();
			ResultSet rs = s.executeQuery("SELECT T FROM TEST");
			rs.next();
			Instant i2 = rs.getTimestamp(1).toInstant();
			System.out.printf("%s %s %s%n", i1, i2, i1.equals(i2));
		}
	}
}

On Java 8 it prints

2018-06-15T03:07:07.496Z 2018-06-15T03:07:07.496Z true

On Java 9 it prints

2018-06-15T03:08:13.634959Z 2018-06-15T03:08:13.634959Z true

On Java 10 it prints

2018-06-15T03:08:50.187309Z 2018-06-15T03:08:50.187309Z true

Please, provide a simple complete test case for it. Do not use assertThat(bar, is(bar2)) with is from Harmcrest in it, use standard assertEquals(bar, bar2) from JUnit or do not use JUnit at all.

@katzyn , thanks for the test case. That is very odd.

Please, provide a simple complete test case for it.

Yes, I will do one as soon as I have time. Now I'm even more intrigued to get to the bottom of this.

Do not use assertThat(bar, is(bar2)) with is from Harmcrest in it, use standard assertEquals(bar, bar2) from JUnit or do not use JUnit at all.

I don't understand your reasoning here, or how it is relevant. Care to explain? At the root of things they both use Object.equals(), don't they?

But anyway, I'll be happy to simply use instant1.equals(instant2). I'll try my best to find some time to look into this soon, because now you have me confused about what is going on.

I don't understand your reasoning here, or how it is relevant. Care to explain?

Harmcrest does not exist in H2 build path, any H2 developer who will try to test it in own development enviroment will be required to resolve this problem somehow. It takes some time, nobody wants to spend it. Your test case with third-party dependencies most likely will be ignored.

Also assertEquals() is a standard and efficient way for comparing values in JUnit. But you can just print results of comparison or values themselves and do not use JUnit at all, it does not matter. It's enough to provide a simple test program (like the sample above) that can reproduce your problem.

Good morning. @katzyn I just stuck your test case above, exactly, into a JUnit @Test. For me it printed:

2018-06-15T13:30:54.600286700Z 2018-06-15T13:30:54.600287Z false

I am running H2 1.4.197 on Windows 10 Professional 64-bit. My Java version is:

java version "10.0.1" 2018-04-17
Java(TM) SE Runtime Environment 18.3 (build 10.0.1+10)
Java HotSpot(TM) 64-Bit Server VM 18.3 (build 10.0.1+10, mixed mode)

I haven't had a chance yet to try the code using main(), but I can't fathom how the JUnit test harness could affect how Instant works. Nevertheless I'll try the code using main(), but that will take me longer.

In the meantime @katzyn could you verify the version of Java 10 you're using and the platform? Running java -version should do it. Thanks.

Here's another output of the same test that's even more interesting!! Look how the value we got back from H2 is actually rounded up!!

2018-06-15T13:53:15.261608900Z 2018-06-15T13:53:15.261609Z false

This relates to something I saw with Oracle as well. Somehow the extra precision is getting rounded up, making the timestamp value I get back from the database later than the one I put in!

If you need more than 6 fractional digits you should always specify fractional seconds precision for TIMESTAMP columns. Something like TIMESTAMP(7) or TIMESTAMP(7) WITH TIME ZONE. Default precision of 6 is required by the SQL standard for these data types.

Both Oracle and H2 support precision from 0 to 9 fractional digits. Some other databases do not support precision above 6.

Windows provides 7 digits by itself. Linux provides 9 digits. Java on Windows returns 7 digits, I didn't know it before because I never tested it. Java on Linux returns only 6 digits, because Java uses an old system function that provides only microseconds instead of better one.

I was cleaning up my inbox and wanted to follow up on this.

Thanks for providing me the information about the default precision for fractional digits; that's not something I had thought of, so I went and researched it. So the default timestamp precision is probably the cause of the problem. I'm not saying that I need more precision, but when Java 9+ enhanced the precision of the timestamps across the JVM on Windows, it causes a problem, so that's something everyone should be aware of.

And it's not just that extra precision is truncated—the way H2 and Oracle store things, apparently they can round up at times! This completely surprised me and complicated the unit tests, which were trying to work around the round-trip problem by truncating at a certain number of fractional digits.

I would urge you not to dismiss it so quickly. I'm not saying there's anything specifically you need to do; I'm just saying I think it's not a trivial problem, and could bite people trying to represent a normal Instant in Java.

Anyway thanks for the discussion and insights.

Yes, they can. SQL standard clearly indicates that implementation-defined rounding or truncation is performed when necessary, so each database system may choose own behavior here. Many databases round these values in the same way as numeric values. In H2 half-up rounding is used.

If your time zone uses DST and your timestamps may be near transitions, I suggest you not to use TIMESTAMP(9) and java.sql.Timestamp, but use TIMESTAMP(9) WITH TIME ZONE for Instant values and pass them as OffsetDateTime or as some other data type that both drivers support. In H2 Instant is supported directly, but I'm not sure that such support exists in Oracle too. TIMESTAMP data type can't distinguish timestamps before and after transition from DST to normal time.

@garretwilson came across this while looking up a probably different timestamp precision issue, but you might be interested in this bug - https://bugs.openjdk.java.net/browse/JDK-8135055 Instants aren't directly involved but java.sql.Timestamp was doing some unexpected stuff under the hood in how it represented the millis/nano precision.