[jboss-jira] [JBoss JIRA] (WFLY-5207) Timestamp rounding issues on Microsoft SQL Server

Timestamp rounding issues on Microsoft SQL Server ------------------------------------------------- Key: WFLY-5207 URL: https://issues.jboss.org/browse/WFLY-5207 Project: WildFly Issue Type: Bug Components: EJB Affects Versions: 10.0.0.Beta2 Environment: all supported Microsoft SQL Server versions (2008, 2012, 2014) Reporter: Jan Martiska Assignee: Jan Martiska Priority: Critical Fix For: 10.0.0.CR1 The DATETIME datatype in SQL Server has a precision of ~0.003 seconds. It sometimes can happen that an EJB timer gets scheduled to a timestamp which will get rounded after its insertion into the database, because the timestamps are generated with precision to miliseconds. After that, the timer service will be unable to locate this row, because it attempts to select using WHERE NEXT_DATE=$timestamp, where $timestamp is the original unrounded java.sql.Timestamp, and this condition no longer holds true. After a timer is scheduled to go off at such unfortunate timestamp, it will never go off again. The solution is to either truncate the milisecond part, or to use the newer DATETIME2 datatype, which has an accuracy of 100 nanoseconds (https://msdn.microsoft.com/en-us/library/bb677335.aspx). DATETIME2 is supported on SQL Server 2008+.