SQL Datetime column not reliable for storing milliseconds

is no new issue. And though there are tons of posted solutions out there I still, brilliantly if I may add, managed to waste 2 hours on this and will now deservingly dedicate a post to it.

The issue occurred when I compared java.util.Date field on java object against it’s persisted hibernate version retrieved from MSSQL table composed by column of type Datetime. It turned out that the loaded object had different value for milliseconds which was failing the comparison.

For example, take the following:

SQL table

CREATE TABLE example
( 
	objectId bigint PRIMARY KEY,
 	dateCol datetime
);

 

Java hibernate entity

package hibernate;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "example")
public class MyObject {

	private long objectId;
	private Date dateCol;

	@Id
	public long getObjectId() {
		return objectId;
	}

	public void setObjectId(long objectId) {
		this.objectId = objectId;
	}

	@Column
	public Date getDateCol() {
		return dateCol;
	}

	public void setDateCol(Date dateCol) {
		this.dateCol = dateCol;
	}
}

 

JUnit test

package hibernate;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;

import java.util.Date;

import org.hibernate.Session;
import org.junit.Test;

public class ObjectTest {

	@Test
	public void persist() {
		Session session = HibernateUtil.getSessionFactory().openSession();

		session.beginTransaction();

		MyObject initialObject = new MyObject();
		initialObject.setDateCol( new Date() );
		initialObject.setObjectId( 1 );
		session.delete( initialObject );

		session.save( initialObject );
		session.getTransaction().commit();

		session.flush();
		session.clear();

		MyObject loadedObject = (MyObject) session.get( MyObject.class, initialObject.getObjectId() );

		assertNotNull( loadedObject );
		assertEquals( initialObject.getDateCol().getTime(), loadedObject.getDateCol().getTime() );
	}
}

The test will usually fail with message similar to:
“java.lang.AssertionError: expected:<1446129615927> but was:<1446129615000>”

What went wrong? Well persistence of course.

It turned out that when java.util.Date’s long representation (or any other date objects’s representation for that matter, as long as it is … long) is stored into Datetime column, the milliseconds were getting truncated because Datetime type is not big enough by having accuracy of only 1/333s of a second (3.33ms). So values that are does not divide precisely will get rounded to increments of .000, .003, or .007 seconds.

Example for MSSQL:

According to Microsoft’s official documentation on Datetime data type if you try to insert “01/01/98 23:59:59.999” into Datetime column, the actual value stored will be rounded to “1998-01-02 00:00:00.000”.

More examples:

User-specified value                   System stored value
01/01/98 23:59:59.999               1998-01-02 00:00:00.000

01/01/98 23:59:59.995               1998-01-01 23:59:59.997
01/01/98 23:59:59.996
01/01/98 23:59:59.997
01/01/98 23:59:59.998

01/01/98 23:59:59.992               1998-01-01 23:59:59.993
01/01/98 23:59:59.993
01/01/98 23:59:59.994

01/01/98 23:59:59.990               1998-01-01 23:59:59.990
01/01/98 23:59:59.991

Since all depends on fortune and rounding, comparing dates may or may not fail. And this is the biggest problem that I have with this deceitfully unharmful issue: tests may or may not assert properly and irreversibly corrupt bigger sets of data.

And don’t think this doesn’t concern you just because you code is not interested in milliseconds. If it stores java.util.Date in a Datetime column then I suggest you look at the first row on the example above and think again.

I was able to reproduce this issue on three of the most popular relational databases: MySQL, Oracle, and MSSQL. All test and source are available for download at the the end of this article.

Solutions

As I mentioned before there are many solutions out there, but most importantly there are solutions provided by DB vendors themselves.

Oracle

In Oracle’s case the solution will be to use Timestamp or Timestamp With Time Zone type instead of Datetime as described this article.

MSSQL

For MSSQL you can use Datetime2 type. Timestamp is also supported, but only one such column is allowed per table so it may not fit your needs.

MySQL

MySQL provides even flexible solution. You can actually define precision for storing milliseconds within Datetime column. The column type will look like this: DATETIME(6).

The Long solution

And of course there is also the “general solution” for storing milliseconds which will fit any DB vendor – the numeric field approach. This is a fairly spread tactic and it involves declaring a column of type number(enough to fit a long value) into which to store the long representation of the date and time with milliseconds. It’s guaranteed to work without any precision loss, but you have to handle the conversion between long and Date wherever necessary.

I don’t like using this approach because select statements will return a large non-descriptive numeric value which may not be what you’ll expect when selecting a date.

Links & Resources

Oracle documentation on DATE, DATETIME, and TIMESTAMP Types;
Very good tutorial for MSSQL’s Datetime2 type;

MySQL’s documentation on Fractional Seconds in Time Values;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s