Extracting only date part of a date time column using JPA/Hibernate

I recently came across a situation where I have to get only the date portion from a column whose data type is “timestamp” (which is mapped using @TimeStamp annotation). After hours of investigation, I found a way to do that using standard jpa/hql queries and hence thought of documenting it in this post.

MySQL has a simple “date() function” that would just give the date part from a date time expression. Microsoft SQL Server offers a couple of ways to achieve the same. Unfortunately, I couldn’t find any standard ways provided by jpa/hibernate to achieve this.

Interestingly, the closest thing that can be considered as “standard” is the “cast function” which many databases seem to support. This prompted me to go for this solution because I felt this might be clean, as the syntax across different databases are pretty much similar.

So I came up with a query something similar to this:


select cast(myDateTimeColumn as date) from MyTable

This worked flawlessly on MySQL! Great..!

But, I quickly ran into problem when hibernate executed the same jpl/hql query against Microsoft SQL Server. Hibernate acted a bit smart and generated the following query (trimmed for clarification):


select cast(myDateTimeColumn as dateTime) from MyTable

That’s not what I expected! That defeats the purpose of the “cast” function itself ;-)

Why the heck Hibernate does this weird stuff? I thought of digging deep to learn more about this behaviour of Hibernate.

Hibernate uses the “CastFunction” class to handle “cast” functions you write in your queries. Upon exploring the class, you will find that this is how the “cast” functions are interpreted,

 return "cast(" + args.get(0) + " as " + sqlType + ')';
 

Where the “sqlType” is determined as follows:

 String sqlType = factory.getDialect().getCastTypeName( sqlTypeCodes[0] );
 

Hibernate stores the type names in a hash map and by default, registers the “Date” type as “datetime” like this:

 registerColumnType( Types.DATE, "datetime" );
 

And that is exactly the reason for the weird behaviour I mentioned in the SQLServerDialect.

Fortunately, hibernate provides hooks to tweak the dialects to support the features that are not otherwise available by default. All we need to do this is to write a custom dialect class by subclassing the appropriate dialect. This is the custom sql server dialect class that I came with:

 package org.hibernate.dialect;

import java.sql.Types;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;

/**
 *
 * @author James Selvakumar
 */
 public class CustomSQLServerDialect extends SQLServerDialect
 {
 private static final Logger logger = LoggerFactory.getLogger(CustomSQLServerDialect.class);

@Override
 public String getCastTypeName(int code)
 {
 String castTypeName = "";
 if (code == Types.DATE) {
 castTypeName = "date";
 } else {
 castTypeName = super.getCastTypeName(code);
 }
 logger.debug("Code: {}, cast type name: {}", code, castTypeName);
 return castTypeName;
 }
 }
 

That’s pretty simple, wasn’t it? We are playing safe here by overriding only the “getCastTypeName” method and returning the cast type name as “date” when the type is Types.DATE. We are delegating the responsibility to the super class for “getCastTypeName” requests other than Types.DATE.

Just update your hibernate configuration to make use of this dialect for sql server datasource.

Fortunately, this solution worked flawlessly for me on both MySQL 5.1 and Microsoft SQL Server 2008 databases. It didn’t work though with HSQLDB 1.8.

I hope this helps some of you. If you find any issues in this article or have any other great idea, consider sharing that in the comments.


1 comment to Extracting only date part of a date time column using JPA/Hibernate

Leave a Reply

  

  

  

*

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>