Closed avernet closed 5 months ago
It isn't a time zone issue, but the problem with timestamp precision. I am reproducing this with standalone code:
val stmt1 = conn.createStatement();
val stmt2 = conn.prepareStatement("SELECT count(*) FROM orbeon_form_data WHERE last_modified_time = ?");
val rs1 = stmt1.executeQuery("SELECT last_modified_time FROM orbeon_form_data")
while (rs1.next()) {
val timestamp = rs1.getTimestamp(1)
stmt2.setTimestamp(1, timestamp)
val rs2 = stmt2.executeQuery();
rs2.next();
println(s"Count: ${rs2.getInt(1)}, timestampSource: ${timestamp}")
}
With the data I have right now in SQL Server, this produces the following output:
Count: 1, timestampSource: 2024-04-27 21:52:50.78
Count: 1, timestampSource: 2024-04-28 13:54:54.63
Count: 0, timestampSource: 2024-04-28 15:58:53.273
Count: 1, timestampSource: 2024-04-29 10:59:08.4
Count: 0, timestampSource: 2024-04-29 10:59:36.517
Count: 1, timestampSource: 2024-04-29 11:03:50.41
Count: 1, timestampSource: 2024-04-29 11:05:32.51
Count: 1, timestampSource: 2024-04-29 11:06:33.01
From https://github.com/microsoft/mssql-jdbc/issues/1843#issuecomment-1149177987:
Comparing a java Timestamp to a datetime column on sql server will not have accurate results.
From https://github.com/microsoft/mssql-jdbc/issues/680#issuecomment-382472960:
Due to the data handling accuracy "improvements" in 2016, datetime values are made more "accurate" when converting to datetime2 since datetime is actually granular to 1/300 of a second.
From: https://github.com/microsoft/mssql-jdbc/issues/1843#issuecomment-1150233477:
When you read value A the server will give the driver a time value which for example ends in .003 seconds. When you query the database against this same column we then pass back that value which ends in .003 seconds, however when the server makes the comparison it will compare the value we passed with the stored value of .00<1 third of a millisecond>. Which will result in an inequality since 0.003 != 0.0033333333...
I think that explains what the issue is. I see two possible fixes, and suggest we implement both:
datetimeParameterType=datetime
to the connection string, per https://github.com/microsoft/mssql-jdbc/pull/1687.DATETIME2(3)
instead of DATETIME2
, the latter defaulting to (7)
.
It looks like a time zone issue to me, but I haven't quite gotten to the bottom of it. A few data points:
java.sql.Timestamp
.java.time.Instant
, which is a UTC time.Instant
to aTimestamp
withTimestamp.from(...)
.+1 from customer