Tag Archives: AbstractSingleColumnStandardBasicType

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

Setting same timezone of the Application Server and of DB Server

Setting timezone of the JVM as that of DB Server

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.

Blog Stats

Categories

Shortcuts

Advertise YOUR BUSINESS Here

Do You want to advertise your Business here? Contact me @ dem.street@gmail.com or leave a message here. Note that this is an informative site with traffic coming from different websites. No Requests from Irrelevant sites, not suitable as per laws or obscenity etc will be entertained