Using Hibernate and Struggling with querying DateTime Column in RDBMS (like MS-SQL) in specific timezone?
No matter what Timezone your DateTime object has, while issuing hibernate query,
do you observe that Time in Default Timezone of JVM is always getting passed and thus not giving you desired results?

If that’s the case, this article describes a process to achieve querying DateTime column with specific timezone.

WHY THIS HAPPENS?

It is because your Application Server and Database Server are running in Different TimeZones.

If your Application Server and Database Server are running in different TimeZones, we need to ensure that the Date Time query parameter values shall be sent as per DB Timezone to get desired results.

Let’s understand how does Hibernate\DB Driver forms a Sql Query in the next section.

HOW HIBERNATE CREATES A QUERY?

On an Application Server, DB Driver forms a Command before sending it to RDBMS. Database System then executes the query (may compile if needed) and return the results accordingly.

DB Driver instantiates a Command in the form of PreparedStatement object. Then, DBConnection is attached with the above Command Object on which this command will be executed. Since we want to query by certain parameters, DateTime in our case, DB Driver sets the query parameters on the command. 

PreparedStatement exposes few APIs to set different parameters depending upon the type of the parameter.
To pass DateTime information, various APIS being exposed are:

• setDate
• setTime
• setTimestamp

All these functions allow passing Calendar object to be passed. Using this Calendar object, Driver constructs the SQL DateTime value.

If this Calendar object is not passed, Driver then uses the DEFAULT TIMEZONE of the JVM running the application. This is where things go wrong and desired results are not obtained.

How can we solve it then?

DIFFERENT APPROACHES

1. Setting same timezone of the Application Server and of DB Server
2. Setting timezone of the JVM as that of DB Server
3. By extending the TimestampTypeDescriptor and AbstractSingleColumnStandardBasicType classes and attaching to the Driver

1st and 2nd Approaches are fine, however these can have side-effects.

1st can impact other applications which are running on the same system. Usually, 1 application runs on a single server in Production or LIVE environment, however, with this we are delimiting the deployment of other applications.

2nd approach is better than 1st one since it will not impact other applications, however, the caveat here is what if your application is talking to different DB Systems which are in different timezones. Or, what if you want to set TimeZone on only few selected Time Fields.

3rd approach is flexible. It allows you to represent different time fields in even different time zones.

AlRight. Can we have steps then to implement Approach #3

STEPS FOR 3rd Approach:

Provide Custom TimestampTypeDescriptor and AbstractSingleColumnStandardBasicType

• Implement Descriptor class as given below:

  import java.sql.CallableStatement;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Timestamp;
  import java.util.Calendar;
  import java.util.TimeZone;
  
  import org.hibernate.type.descriptor.ValueBinder;
  import org.hibernate.type.descriptor.ValueExtractor;
  import org.hibernate.type.descriptor.WrapperOptions;
  import org.hibernate.type.descriptor.java.JavaTypeDescriptor;
  import org.hibernate.type.descriptor.sql.BasicBinder;
  import org.hibernate.type.descriptor.sql.BasicExtractor;
  import org.hibernate.type.descriptor.sql.TimestampTypeDescriptor;
  
  /**
   * Descriptor for {@link Types#TIMESTAMP TIMESTAMP} handling with zone.
   */
  public class CustomZonedTimestampDescriptor extends TimestampTypeDescriptor {
      public static final CustomZonedTimestampDescriptor PST_INSTANCE = new CustomZonedTimestampDescriptor();
  
      /**
       * Instantiate an object of CustomZonedTimestampDescriptor with Timezone set to "America/Los_Angeles"
       */
      public CustomZonedTimestampDescriptor() {
          this.calendar = Calendar.getInstance(TimeZone.getTimeZone("America/Los_Angeles"));
      }
  
      /**
       * Instantiate an object of CustomZonedTimestampDescriptor
       * @param zone Timezone to be used
       */
      public CustomZonedTimestampDescriptor(TimeZone zone) {
          this.calendar = Calendar.getInstance(zone);
      }
  
      /**
       * Get the binder (setting JDBC in-going parameter values) capable of handling values of the type described by the
       * passed descriptor.
       *
       * @param javaTypeDescriptor The descriptor describing the types of Java values to be bound
       *
       * @return The appropriate binder.
       */
      @Override
      public <X> ValueBinder<X> getBinder(final JavaTypeDescriptor<X> javaTypeDescriptor) {
          return new BasicBinder<X>( javaTypeDescriptor, this ) {
              @Override
              protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options) throws
                      SQLException {
                  st.setTimestamp(index, javaTypeDescriptor.unwrap(value, Timestamp.class, options), calendar);
              }
          };
      }
  
      /**
       * Get the extractor (pulling out-going values from JDBC objects) capable of handling values of the type described
       * by the passed descriptor.
       *
       * @param javaTypeDescriptor The descriptor describing the types of Java values to be extracted
       *
       * @return The appropriate extractor
       */
      @Override
      public <X> ValueExtractor<X> getExtractor(final JavaTypeDescriptor<X> javaTypeDescriptor) {
          return new BasicExtractor<X>( javaTypeDescriptor, this ) {
              @Override
              protected X doExtract(ResultSet rs, String name, WrapperOptions options) throws SQLException {
                  return javaTypeDescriptor.wrap(rs.getTimestamp(name, calendar), options);
              }
  
              @Override
              protected X doExtract(CallableStatement statement, int index, WrapperOptions options) throws SQLException {
                  return javaTypeDescriptor.wrap(statement.getTimestamp(index, calendar), options);
              }
  
              @Override
              protected X doExtract(CallableStatement statement, String name, WrapperOptions options)
                      throws SQLException {
                  return javaTypeDescriptor.wrap(statement.getTimestamp(name, calendar), options);
              }
          };
      }
  
      private final Calendar calendar;
  }
  

  In the above code, Default constructor uses PST Timezone by default. For other TimeZones, simply use the Parameterized Constructor.

• Implement Type class and use the above Descriptor class

  import com.expedia.www.air.commission.migration.db.descriptors.CustomZonedTimestampDescriptor;
  
  import java.util.Comparator;
  import java.util.Date;
  import java.util.TimeZone;
  
  import org.hibernate.dialect.Dialect;
  import org.hibernate.engine.spi.SessionImplementor;
  import org.hibernate.type.AbstractSingleColumnStandardBasicType;
  import org.hibernate.type.LiteralType;
  import org.hibernate.type.TimestampType;
  import org.hibernate.type.VersionType;
  import org.hibernate.type.descriptor.java.JdbcTimestampTypeDescriptor;
  
  /**
   * A type that maps between {@link java.sql.Types#TIMESTAMP TIMESTAMP} and {@link java.sql.Timestamp} with zone
   */
  public class CustomZonedTimestampType extends AbstractSingleColumnStandardBasicType<Date>
          implements VersionType<Date>, LiteralType<Date> {
      /**
       * Instantiate an object of CustomZonedTimestampType with Timezone set to "America/Los_Angeles"
       */
      public CustomZonedTimestampType() {
          this(CustomZonedTimestampDescriptor.PST_INSTANCE);
      }
  
      /**
       * Instantiate an object of CustomZonedTimestampType
       * @param zone Timezone to be used
       */
      public CustomZonedTimestampType(TimeZone zone) {
          super(new CustomZonedTimestampDescriptor(zone), JdbcTimestampTypeDescriptor.INSTANCE);
      }
  
      /**
       * Returns the abbreviated name of the type.
       * @return String the Hibernate type name
       */
      @Override
      public String getName() {
          return TimestampType.INSTANCE.getName();
      }
  
      /**
       * Convert the value into a string representation, suitable for embedding in an SQL statement as a
       * literal.
       * @param value The value to convert
       * @param dialect The SQL dialect
       * @return The value's string representation
       * @throws Exception Indicates an issue converting the value to literal string.
       */
      @Override
      public String objectToSQLString(Date value, Dialect dialect) throws Exception {
          return TimestampType.INSTANCE.objectToSQLString(value, dialect);
      }
  
      /**
       * Generate an initial version.
       * @param session The session from which this request originates.
       * @return an instance of the type
       */
      @Override
      public Date seed(SessionImplementor session) {
          return TimestampType.INSTANCE.seed(session);
      }
  
      /**
       * Increment the version.
       * @param current the current version
       * @param session The session from which this request originates.
       * @return an instance of the type
       */
      @Override
      public Date next(Date current, SessionImplementor session) {
          return TimestampType.INSTANCE.next(current, session);
      }
  
      /**
       * Get a comparator for version values.
       * @return The comparator to use to compare different version values.
       */
      @Override
      public Comparator<Date> getComparator() {
          return getJavaTypeDescriptor().getComparator();
      }
  }
  

• Add an annotation @Type on the fields for which proper Timezone has to be used

  @Entity
  public class MyEntityClass implements Serializable {
      public static final String CUSTOMIZED_TIMESTAMP_TYPE = "com.db.types.CustomZonedTimestampType";
  
      public Date getUpdateDate() {
          return updateDate;
      }
  
      public void setUpdateDate(Date updateDate) {
          this.updateDate = updateDate;
      }
  
      @Type(type = CUSTOMIZED_TIMESTAMP_TYPE)
      private Date updateDate;
  }
  

As per the above code, MyEntityClass has a field named upDateDate for which we want to send the date and time in correct Timezone.

ADVANTAGES

• Reliability: Expected Results from DB are obtained
• No other application running on a same system is impacted
• Most importantly, the above steps enable a code to be run on any system set in any TimeZone