[FEATURE REQUEST] Support Client-Side rounding for DATETIME

[ecki]

Problem: rounded DATETIME timestamps do not match

When using the old DATETIME data type in SQL Server some precision of timestamps is lost. This is fixed with DATETIME2, however there are situations where an installation needs to keep the old data type DATETIME.

With the jTDS driver this is not a big problem, the following code will insert a timestamp and match the same timestamp:

given: CREATE TABLE T1(id int, d1 DATETIME, d2 DATETIME2)

p = c.prepareStatement("INSERT T1(1,?,?);");
p.setTimestamp(1, new Timestamp(12345));
p.setTimestamp(2, new Timestamp(12345));
p.execute();

p = c.prepareStatement("SELECT * from T1 where D1=?");
p.setTimestamp(1, new Timestamp(12345));

sqlcmd (after code run with jtds driver):

id          d1                      d2
----------- ----------------------- --------------------------------------
          2 1970-01-01 01:00:12.347            1970-01-01 01:00:12.3470000

as you can see, the rounding is done on insert not only for the d1 column but also in the more precise d2.

The same insert with the MSSQL driver looks better as it uses the full datetime2 portential:

id          d1                      d2
----------- ----------------------- --------------------------------------
          2 1970-01-01 01:00:12.347            1970-01-01 01:00:12.3450000

The only problem is that the select does not match this row on d1 when given the same timestamp as was inserted,

(whats worse the new Timestamp(123457) (rounded) value (as read back from the DB) also does not compare with later compat levels, see code below)

I have seen there is some casting (DATETIME2?) going on in the raw sent prepared statements, maybe this is the reason, or maybe just that jTDS does the rounding client side or using another data type to send?

Preferred Solution: round/cast datetime bind parameters

It would be good that for legacy applications (datetime) a mode can be enabled which makes the INSERT/SELECT cycle work with mssql-jdbc regardless of the SQL Server compat level. on datetime columns.

Not exactly sure how this is technically done (casting or rounding) and if it can be done with low impact depending on the column type. If it cant know the column type and dynamically figure it out at least a connection parameter to switch to sending timestamps compatible with datetime 2 would be good.

Describe alternatives you've considered

Alternatives are staying with jTDS (which has other problems), rounding in the application (ugly in codebase) or changing the data type. The last would be possible (and best solution in the long run), but it is a larger project to make that for the large installation base.

Sampe Output (jTDS)

(timezone is UTC+01:00)

Connected: Microsoft SQL Server 15.00.4102 ( 15. 0 ) with jTDS Type 4 JDBC Driver for MS SQL Server and Sybase(1.3.1)
create table...
java.sql.SQLException: There is already an object named 'T1' in the database.
  Details: errorCode:2714 sqlstateS0001
insert data... 1970-01-01 01:00:12.345
query by d2 (datetime2) = 1970-01-01 01:00:12.345
    2        1970-01-01 01:00:12.347        1970-01-01 01:00:12.347

Execute DATETIME test on database with compatibility level 150
 Read by id: 1970-01-01 01:00:12.347 (class java.sql.Timestamp ms=12347.347000000)
 Read by d1 WHERE (read) 2 | 1970-01-01 01:00:12.347 | 1970-01-01 01:00:12.347
 Read by d1 where (initial-ts) 2 | 1970-01-01 01:00:12.347 | 1970-01-01 01:00:12.347

Execute DATETIME test on database with compatibility level 100
 Read by id: 1970-01-01 01:00:12.347 (class java.sql.Timestamp ms=12347.347000000)
 Read by d1 WHERE (read) 2 | 1970-01-01 01:00:12.347 | 1970-01-01 01:00:12.347
 Read by d1 where (initial-ts) 2 | 1970-01-01 01:00:12.347 | 1970-01-01 01:00:12.347

Sample Output mssql-jdbc

Connected: Microsoft SQL Server 15.00.4102 ( 15. 0 ) with Microsoft JDBC Driver 9.2 for SQL Server(9.2.0.0)
create table...
insert data... 1970-01-01 01:00:12.345
query by d2 (datetime2) = 1970-01-01 01:00:12.345
    2        1970-01-01 01:00:12.347        1970-01-01 01:00:12.345

Execute DATETIME test on database with compatibility level 150
 Read by id: 1970-01-01 01:00:12.347 (class java.sql.Timestamp ms=12347.347000000)
 ?? returned timestamp 1970-01-01 01:00:12.347 does not match
 ?? initial timestamp 1970-01-01 01:00:12.345 does not match

Execute DATETIME test on database with compatibility level 100
 Read by id: 1970-01-01 01:00:12.347 (class java.sql.Timestamp ms=12347.347000000)
 Read by d1 WHERE (read) 2 | 1970-01-01 01:00:12.347 | 1970-01-01 01:00:12.345
 ?? initial timestamp 1970-01-01 01:00:12.345 does not match

Sample Reproducer

(works with url1 (jtds) to list a match in both cases but not with url2 (mssql))

package net.eckenfels.tests.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

public class MSSQLDateTime2
{
    public static void main(String[] args) throws SQLException, ClassNotFoundException
    {
        String url1 = "jdbc:sqlserver://bernd1;user=SEEASDB0;password=<pass>;databaseName=SEEASDB0";
        String url2 = "jdbc:jtds:sqlserver://bernd1;user=SEEASDB0;password=<pass>;autoCommit=true;databaseName=SEEASDB0";
        Connection c = DriverManager.getConnection(url1);

        //long ts = 12340; // exact 1970-01-01 01:00:12.34
        //long ts = 12343; // exact 1970-01-01 01:00:12.343
        long ts = 12345; // rounds 1970-01-01 01:00:12.347

        Statement sddl = c.createStatement();

        System.out.println("create table...");
        try {
            sddl.execute("CREATE TABLE T1(id int, d1 DATETIME, d2 DATETIME2);");

        }
        catch (SQLException ignore)
        {
            printException(ignore);

            PreparedStatement p = c.prepareStatement("TRUNCATE TABLE T1");
            p.execute();
            p.close();
        }
        finally {
            sddl.close();
        }

        System.out.println("insert data... " + new Timestamp(ts));
        try (PreparedStatement p = c.prepareStatement("INSERT T1(id,d1,d2) VALUES (2,?,?);");) {
            p.setTimestamp(1, new Timestamp(ts));
            p.setTimestamp(2, new Timestamp(ts));
            p.execute();
        }

        System.out.println("query by d2 (datetime2) = " + new Timestamp(ts));
        try (PreparedStatement p = c.prepareStatement("SELECT id, d1, d2 from T1 where d2=?");) {
            p.setTimestamp(1,  new Timestamp(ts));
            try (ResultSet rs = p.executeQuery();) {
                while (rs.next()) System.out.printf("%5s %30s %30s%n", rs.getString(1), rs.getTimestamp(2), rs.getTimestamp(3));
            }
        }

        /*
        try (Statement s = c.createStatement();) {
            try (ResultSet rs = s.executeQuery("SELECT name, compatibility_level FROM sys.databases");) {
                while (rs.next()) System.out.println(rs.getString(1) + " compatibility level: " + rs.getInt(2));
            }
        }*/

        System.out.printf("%nExecute DATETIME test on database with compatibility level 150%n");
        readAndCompareTimestamp(c, 150, "SEEASDB0", new Timestamp(ts));

        System.out.printf("%nExecute DATETIME test on database with compatibility level 100%n");
        readAndCompareTimestamp(c, 100, "SEEASDB0", new Timestamp(ts));
    }

    private static void readAndCompareTimestamp(Connection c, int  compatibilityLevel, String dbname, Timestamp ts)
        throws SQLException
    {
        try (Statement s = c.createStatement();) {
        s.executeUpdate("ALTER DATABASE " + dbname + " SET COMPATIBILITY_LEVEL = " + compatibilityLevel); }

        Timestamp timestampFromDB = null;
        try (PreparedStatement p = c.prepareStatement("SELECT d1 from T1 where id=?");) {
            p.setInt(1, 2);
            try (ResultSet rs = p.executeQuery();) {
                rs.next();
                timestampFromDB = rs.getTimestamp(1); //get timestamp from datetime column
            }
        }

        System.out.println(" Read by id: " + timestampFromDB + " (" + timestampFromDB.getClass() + " ms="   + timestampFromDB.getTime() + "." + timestampFromDB.getNanos()+")" );

        //execute SELECT with timestamp from DB in where clause
        try (PreparedStatement p = c.prepareStatement("SELECT * from T1 where d1=?");) {
            p.setTimestamp(1, timestampFromDB);
            try (ResultSet rs = p.executeQuery();) {
                if (rs.next()) System.out.println(" Read by d1 WHERE (read) " + rs.getInt(1) + " | " + rs.getTimestamp(2) + " | " + rs.getTimestamp(3));
                else System.out.println(" ?? returned timestamp " + timestampFromDB + " does not match");
            }
        }

        //execute SELECT with initial timestamp
        try (PreparedStatement p = c.prepareStatement("SELECT * from T1 where d1=?");) {
            p.setTimestamp(1, ts);
            try (ResultSet rs = p.executeQuery();) {
                if (rs.next()) System.out.println(" Read by d1 where (initial-ts) " + rs.getInt(1) + " | " + rs.getTimestamp(2) + " | " + rs.getTimestamp(3));
                else System.out.println(" ?? initial timestamp " + ts + " does not match");
            }
        }
    }

    private static void printException(SQLException dt)
    {
        System.out.println("" + dt);
    }
}

[ecki]

This seems to be the different castings the two drivers use (from profiler of other timestamp):

MS JDBC driver:

exec sp_executesql N'SELECT * FROM t2 WHERE col=@P0',N'@P0 datetime2','2021-02-15 14:59:53.2830000'

JTDS JDBC driver;

declare @p1 int
set @p1=2
exec sp_prepare @p1 output,N'@P0 datetime',N'SELECT * FROM t2 WHERE col= @P0 ',1
select @p1
exec sp_execute 2,'2021-02-15 14:59:53.283'

[ecki]

Somewhat related to #680

[lilgreenbird]

hi @ecki

Thank you for the suggestion, the team will review this.

[ecki]

Thank you for the suggestion, the team will review this.

Thanks for response. I understand from #680 that it might be hard to cater for the changed server behavior, but it looks like if you would "just" cast the literal to the actual type (datetime not datetime2) that it would solve the problems.

[ecki]

I have updated the reproducer (program and output) to make the problem a bit clearer. You can see that jtds driver matches all 4 query conditions on the datetime column and that mssql-jdbc matches only 2 (for older compat level) and none for recent compat levels. (This now looks more like a bug, not only a feature request!)

[ecki]

Does it make sense to propose a patch for this? If so, any specific issues to look out for?

[Jeffery-Wasty]

#1687 resolves this by allowing users to specify the datatype to use for date/timestamp parameters. @ecki, please let us know if this fix (included in 12.2+) works for you. Closing issue, but can reopen if needed.