Cutting off DateTime precision?

[ntwilson]

Hi, I haven't been able to verify that this is a SqlHydra issue and not a SqlKata issue, but I'm having some DateTimes getting truncated to the second in my queries.
I've created a query expression to log a select query when executing it:

let private stringifyQuery (query:SqlKata.Query) =
    let compiler = SqlKata.Compilers.SqlServerCompiler()
    let sql = compiler.Compile(query)
    let bindings = 
      String.concat "; " 
        [ 
          for KeyValue (k, v) in sql.NamedBindings -> 
            match v with 
            | :? DateTime as d -> sprintf "(%A: %s)" k (d.ToString("yyyy-MM-dd hh:mm:ss.fffffff"))
            | _ -> sprintf "(%A: %A)" k v
        ]
    sprintf "'%s' with parameters '%s'" sql.Sql bindings

type LoggingSelectBuilder<'a, 'b, 'c when 'c :> Common.DbDataReader> (reader : ('c -> unit -> 'a), ct:ContextType) = 
  inherit SelectAsyncBuilder<'a, 'b, 'c>(reader, ct)

  member this.Run(state: QuerySource<'a, SqlKata.Query>) =
    printfn "%s" <| stringifyQuery state.Query
    base.Run(state)

let loggingSelect reader connStr = 
  LoggingSelectBuilder(reader, (ContextType.Create (openContext connStr)))

I have a query like:

  let! myCount = 
    loggingSelect MyDB.HydraReader.Read connStr {
      for record in table<MyDB.dbo.MyTable> do
      where (record.entry_timestamp > ts)
      select (minBy record.entry_timestamp)
    } 
    |>> Array.ofSeq
    |>> Array.head

  printfn "results in: %s" (myCount.ToString("yyyy-MM-dd hh:mm:ss.fffffff"))

which outputs:

'SELECT MIN([MyTable].[entry_timestamp]) FROM [MyTable] WHERE ([MyTable].[entry_timestamp] > @p0)' with parameters '("@p0": 2022-07-15 09:40:42.6078010)'

results in: 2022-07-15 09:40:42.6077917

it seems like I have to add a full second to the timestamp in the query to get the results I want.
Do you think this is a SqlKata issue or a SqlHydra issue? Do you have any recommendations for how to debug this? A SqlKata developer claims that SqlKata sends raw DateTime parameters to the DB server unaltered in this issue, and is correct as far as what I can tell from the SqlKata source. But I also can't see in SqlHydra source anywhere that the truncation would be happening.

[JordanMarr]

The loggingSelect builder is a neat idea!

I subscribe to the SqlKata repo and noticed your post there. SqlHydra just boxes all values and stuffs them into query parameters, so it shouldn't affect the precision in any way.

Could it be that your columns are using datetime instead of datetime2 data type? I know that datetime2 has more precision than datetime.

[ntwilson]

I've checked my databases. I'm copying data from one database to another. Both databases are using DATETIME2, but maybe it's interesting that I'm retrieving a timestamp from one database that's using DATETIME2(6), and then feeding that timestamp into the query that I showed in the issue description, which is on a database using DATETIME2(7). (I'm adding TimeSpan.FromSeconds(1e-6) to account for the loss of precision). The two separate databases have separate SqlHydra generated schema files - I'm not trying to reuse one schema across the two different databases. I can't imagine how that would affect anything, besides just making the last of the 7 decimal places in my query always a 0.

So I can just add a full second instead of TimeSpan.FromSeconds(1e-6) to get the behavior I want, but I wish I could understand why the query isn't working the way I expect, and returning a timestamp that's less than the timestamp that I'm trying to specify > in my query.

I can execute the query that gets logged in mssql-cli and I get a different answer than what my code gives

[JordanMarr]

You might want to create a test that recreates the data transfer of a date from DB1 to DB2 using vanilla ADO.NET to see if the problem still exists without any other libraries in the middle.

[ntwilson]

Ok, I'll work on creating an open source repo that uses vanilla ADO.NET, then raw SqlKata, then SqlHydra to see where the problem is introduced. If you want to close this issue you can (and if it's a SqlHydra problem, I can open a new issue with the repro), or if you want I can post my results here.

[ntwilson]

Okay, I've reproduced the problem here https://github.com/ntwilson/ado-sql-kata-sql-hydra-timestamp-precision. If you run SetupDatabase.sql on a mssql server (and update the connection string), you'll see that SqlKata and SqlHydra are giving different answers for what I think should be the exact same query. The right answer if full precision is kept is to grab the later timestamp (ending in .2345678), but if it loses precision, it grabs the earlier timestamp (ending in .1234567). When I run dotnet run, I get

Microsoft.Data.SqlClient with DATETIME2: 2022-07-20 15:05:45.2345678
Microsoft.Data.SqlClient with DATETIME: 2022-07-20 15:05:45.1234567
SqlKata: 2022-07-20 15:05:45.2345678
SqlHydra: 2022-07-20 15:05:45.1234567

It's particularly interesting because I initially was getting the wrong answer from SqlKata too, but then discovered that SqlKata uses Dapper, and a DATETIME2 precision issue was logged in the Dapper repo: https://github.com/DapperLib/Dapper/issues/1511 There's a workaround there specifically if all of your DB datetime types are DATETIME2 (which mine are):

Dapper.SqlMapper.AddTypeMap(typeof<DateTime>, DbType.DateTime2)

and when I add that, it makes the SqlKata side of things start working, but SqlHydra still has the same behavior and gives me the loss of precision.

[JordanMarr]

Nice work on the testing! It looks as though this issue can be resolved in SqlHydra. A similar mechanism is already in place for SqlHydra.Npgsql jsonb types that we can use here. I think it should be pretty straight forward change.

[ntwilson]

Can I take a stab at a PR? I'm looking at the jsonb code and I think have a pretty decent idea of what I'm doing

[JordanMarr]

Yeah, that would be great!

Hopefully a change will not be necessary on the SqlHydra.Query side. It's a little crazy because we are setting the parameter type using reflection to avoid having to reference all the ADO.NET provider libraries.

Just let me know if you have any questions.

[ntwilson]

Okay, I could use a little help on this one. The code change seems simple enough, but I'm not terribly familiar with docker dev containers and VS Code Remote-Container, so I'm fighting a bit with the testing. I've got the docker image launched in VS Code Remote-Container, and I'm able to connect to my localhost mssql instance. I've created a new test table with a datetime2, but if I do a git status, I don't see any changes to the mssql/Dockerfile. How do I make database changes persist and get committed to the repo?

Sorry for basically asking for a docker dev container tutorial 😕

[JordanMarr]

No worries, that stuff is hard. I personally never use the devcontainer setup, mostly because I prefer using Visual Studio over vscode. So I just do docker-compose up instead and then use VS.

As for modifying the table, maybe you can append the new table create script to the install.sql file and then re-run the script?

At least that's what we did for the test tables created in the postgres install.sql file.

[JordanMarr]

Released: https://github.com/JordanMarr/SqlHydra/releases/tag/mssql-datetime2-fix