CODEWITHSUNDEEP
javasql-serverscalatimestampunix-timestamp
djsumdog
djsumdog

Reputation: 2710

UNIX Timestamp to Datetime inconsistencies with MS SQL

I currently develop for BigSense and we support three database backends: MySQL, Postgres and MS SQL. Recently I ran into an interesting times/query issue when writing test cases.

Given the following UNIX timestamp: 1499839328918

And given the following data types:

MySQL: DATETIME(6)
Postgres: TIMESTAMP WITHOUT TIME ZONE
Microsoft SQL: DATETIME

I use the following function to convert the UNIX timestamp to a java.sql.Timestamp

  def timestampToDate(unixTimeStamp: String) : java.sql.Timestamp = new java.sql.Timestamp(unixTimeStamp.toLong)

In the Scala REPL, this seems to give me the following date:

scala> timestampToDate("1499839328918")
res0: java.sql.Timestamp = 2017-07-12 06:02:08.918

However when I do an INSERT and then a SELECT, Postgres/MySQL give me the correct time stated above, however with Microsoft SQL, I get the following: 2017-07-12 06:02:08.917

So it's off by 1/100 of a second, and normally I wouldn't care, but I want my automated tests to pass without having to fudge the 100s position of the timestamp. I've tried a couple of other timestamps and they all seem to be off for MS SQL. What is going on here? Is it an issue with the Microsoft JDBC driver or does MS SQL do weird timestamp rounding?

EDIT I'm using "net.sourceforge.jtds" % "jtds" % "1.3.1",

Upvotes: 0

Views: 271

Answers (1)

ugo
ugo

Reputation: 284

As far as I know a MsSQL DATETIME data type, has a strange resolution, as milliseconds are rounded to 0, 3 or 7, so it appear that you have fallen in one of theese case.
You may want to use DATETIME2 data type, (starting from SqLServer2008, I guess), that has a resolution of 100ns.
While hours differences should depends on server time-zone.

Upvotes: 1

Related Questions