Sql Server datetime
I was working on a POC in an application to trigger runs which would update status tables at both Postgres and SqlServer. Well, it was more than that but that is the problem statement I am going to focus on for this post.
We were using Spring batch for job framework. And the intent is to assign an unique timestamp for each runs — it is guaranteed to be unique because the application ensures that there is only one active job at any given time.
This unique timestamp is then assigned to the run_timestamp column in bunch of status tables that are spread over postgres and sql server — we had no control over the schema of these tables.
When I was running test to see if the status tables are updated at both databases, the test failed randomly because the records were not found in the sql server ones. Turns out, it is because of SqlServer’s datetime.
Looks like SqlServer datetime accuracy is that the timestamp is rounded to increments of .000, .003, or .007 seconds 🤷♀️ So what my application calls as “current time” is actually truncated at SqlServer status table and not at Postgres. So pivoting by this “unique_timestamp” is not a choice unless you truncate the timestamp at application itself so that the timestamp equality check succeeds for both databases. Something like this :
Instant now = Instant.now();
long millis = now.truncatedTo(ChronoUnit.MILLIS).toEpochMilli();
long roundedForSqlServer = (millis / 10) * 10;Note that in my case it is assured that there is only one active run and runs are minutes apart. And even if they are not, there is no way each run can finish in less than second. Thankfully, it was dropped at POC stage itself because there were way too many hurdles dealing with directly updating the database owned by some other team. So I didn’t get to dig deep into this. Nevertheless a good lesson to look into precision of timestamps 👀
But notice the implication of truncating current time - when these timestamps, that are supposed to be unique, are just 1 millisecond apart.
int counter = 0;
while(counter < 5) {
Instant now = Instant.now();
System.out.println(now + " "
+now.truncatedTo(ChronoUnit.MILLIS).toEpochMilli()+ " "
+(now.truncatedTo(ChronoUnit.MILLIS).toEpochMilli()/10 )* 10
);
Thread.sleep(1);
counter++;
}OUTPUT:2020-07-26T11:16:07.669485200Z 1595762167669 1595762167660
2020-07-26T11:16:07.708465800Z 1595762167708 1595762167700
2020-07-26T11:16:07.710224700Z 1595762167710 1595762167710
2020-07-26T11:16:07.711458700Z 1595762167711 1595762167710
2020-07-26T11:16:07.713491700Z 1595762167713 1595762167710
Notice how for the last 3 values, even though the timestamp is actually different,result in same value due to truncation.
