How to assign Date parameters to Hibernate query for current timezone?

When you assign a date to a named SQL parameter Hibernate automatically converts it to GMT time. How do you make it use the current server timezone for all dates?

Lets say you have a query:

Query q = session.createQuery("from Table where date_field < :now");
q.setDate("now", new java.util.Date());

"now" will be set to GMT time, while "new Date()" gets your current server time.


Asked by: Robert393 | Posted: 28-01-2022

Answer 1

As it turned out Hibernate doesn't convert dates to GMT automatically, it just cuts off time if you use query.setDate(), so if you pass "2009-01-16 12:13:14" it becomes "2009-01-16 00:00:00".

To take time into consideration you need to use query.setTimestamp("date", dateObj) instead.

Answered by: Elise799 | Posted: 01-03-2022

Answer 2

Hibernate is ignorant of timezones. Any timezone conversion should be done prior to executing the query.

E.g., if your database server is set to CST, but the user is on EST, you'll need to add 1 hour to any timestamps which are the input to a query.

Answered by: Roland820 | Posted: 01-03-2022

Answer 3

We use a custom Hibernate date type. Any time we set a parameter on a query we use a base class or utility method so we can pass the user's timezone into the custom type parameter.

You could get by with just manually adjusting the time in the utility method for queries, but this way dates that are read in or written to the database are also correctly converted. This method also handles the situation where the database stores the date in its local time zone. So even if you have a user in one time zone, a database server in another, and Java using GMT, it can get everything straight. It ends up looking like:

Properties properties = new Properties();
properties.setProperty("timeZone", databaseTimeZone);
query.setParameter("from", dateEnteredByUser, Hibernate.custom(LocalizedDateType.class, properties));

As an added bonus, we use this to deal with the fact that SQL Server converts 23:59:59.999 to the next day. In the custom type we check for that and back it off.

Answered by: Julian414 | Posted: 01-03-2022

Answer 4

If you need a timeZone synchronized value you can use now() in your HQL. Also I suggest you to use the Joda library. It has a hibernate-plugin module.

Answered by: John955 | Posted: 01-03-2022

Answer 5

Hibernate team has deprecated setTimestamp(String name, Date val) method as well as org.hibernate.Query interface since version 5.2:

 /* @deprecated (since 5.2) use {@link #setParameter(int, Object)} or
    {@link #setParameter(int, Object, Type)}  instead

So you can use below code:

import org.hibernate.query.Query;
Query query = session.createQuery("from Table where date_field < :now");
query.setParameter("now", new Date(), TimestampType.INSTANCE );

It's notable that setDate method which cuts down time portion is also deprecated and can be replaced with this option(from the same interface org.hibernate.query.Query):

query.setParameter("firstMomentOfToday", new Date(), DateType.INSTANCE);

If you are going to use it with java 8 and the relevent jdbc drivers like Oracle 'ojdbc8.jar' and supported hibernate versions this might not work as expected, so it's wise to use new java.time.LocalDate and LocalDateTime instead.

Answered by: Hailey111 | Posted: 01-03-2022

Answer 6

In Hibernate 6, using jakarta.persistence, it is

query.setParameter("now", new Date(), TemporalType.DATE );

Answered by: Rafael790 | Posted: 01-03-2022

Similar questions

java - Optional parameters with named query in Hibernate?

Is there any way to specify optional parameters (such as when search parameters are provided from a form and not all parameters are required) in a named query when using Hibernate? I'm using a native SQL query, but the question is probably applicable to named

java - Hibernate Native Query problem with named parameters

I have a problem with Hibernate Native Query. I have one SELECT that selects array slice (PostgreSQL database). The problem is that hibernate recognizes the following part: ":300" from "SELECT my_array[1:300]..." as named parameter and I get the following exception: Not all named parameters have been set. I tried to escape the colon (:) with ':' , '::' but with no success. The Hibernate version is ...

java - Hibernate - strange order of native SQL parameters

I am trying to use native MySQL's MD5 crypto func, so I defined custom insert in my mapping file. &lt;hibernate-mapping package="tutorial"&gt; &lt;class name="com.xorty.mailclient.client.domain.User" table="user"&gt; &lt;id name="login" type="string" column="login"&gt;&lt;/id&gt; &lt;property name="password"&gt; &lt;column name="password" /&gt; &lt;/property&gt; &lt;sql-insert&gt;I...

java - Hibernate IN query with two parameters

I need to construct a hibernate query that would do something like this: select p from Person p where (, p.surname) in (('Peter', 'Black'), ('Thomas', 'Peterson'), ('Julia', 'Cook')) The length of the name/surname list is not defined upfront and the name and surname can contain any characters (the name and surname naming is used just for the illustration purposes) Also I woul...

java - How to get Hibernate (3.6/4.0) JDBC bind parameters?

We would like to get a hold of all the parameters that hibernate binds to SQL statments before they are sent to the sql server. Is that possible? We're using an Interceptor the get the SQL from Hibernate: public class SQLInteceptor extends EmptyInterceptor { @Override public String onPrepareStatement(String sql) { String prepared = super.onPrepareStatement(sql); ..... ...

java - Hibernate parameters injection inside mapping, possible?

I'm using Hibernate in many projets. Since time I've developped my projects including a translation table that containts text values in every language for every other tables that need it. Mapping file for the translation table &lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "

java - set table name with hibernate name parameters

I need to set a table name dynamically so that I use query.setText(tname,abc) e.g: select from :tname where = '2' I used setText() because when I use setString() it says "tname is a invalid parameter" because I assume that Hibernate adds '' when setting string parameters. But even setText() does not help and gi...

java - Hibernate dynamic parameters query

I have a query that need to use "dynamic" parameters set. But coundn´t realize how to do it, i tried to not add the where string but it gives me erros because the parameter is seted. Any tips? String queryS = "select object(c) from " + entityClassName + " as c " + "where 1 = 1" ; if(codigoPaciente.compareTo("") != 0) { ...

java - Limit hibernate stored procedure parameters

I'm using hibernate in my application and when persisting a large set (~450 objects each with 6 or 7 fields) for the first time hibernate tries to persist it all in one go and results in a sqlGrammarException with a message "The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request." This seems like something that ...

java - How to search with multiple parameters in Hibernate Search

Using: Hibernate Hibernate-Search Hibernate-Anotations Hibernate-Commons-Cnnotations Lucene-Core 2.9.4 Lucene-Analyzers 2.9.4 Lucene-Queryparser 2.9.4 How can search with multiple parameters like: SELECT * FROM example WHERE column1 = &quot;text1&quot; AND (column2 = &quot;text2&quot; OR col...

java - Passing parameters between JSPs

Please bear with me here, I'm a student and new to Java Server Pages. If I'm being a complete idiot, can someone give me a good link to a tutorial on JSP, since I've been unable to find info on this anywhere. Okay, here goes... I'm using Netbeans and trying to pass an object that connects to a database between the pages, otherwise I'd have to reconnect to the database every time a new page is displayed. ...

c# - How to analyse .exe parameters inside the program?

I have a program that can have a lot of parameters (we have over +30 differents options). Example: myProgram.exe -t alpha 1 -prod 1 2 -sleep 200 This is 3 Commands (from command pattern object at the end) that each contain some parameters. Inside the code we parse all command (start with -) and get a list of string (split all space) for the parameters. So in fact, we have : string-->Collection ...

java - How can I validate parameters without creating a separate dialog?

I'm going to make an application (in Swing) that uses a tree to visualize a data structure (JTree). A tree will be on the left side of a window. The user will be able to browse a tree. The parameters of every tree node will be displayed on the right side of the window. The example windows will be looking like this =========================== | tree panel | data panel | | | | | ...

Java String Parameters

I'm coming from a .net background and want to know the accepted way of creating a method that returns a boolean and modifies a string that was passed in via parameter. I understand Strings are immutable in Java so the below snippet will always produce an empty string. I am constrained to return boolean only. Exceptions can't be thrown. If I need to wrap the String class in, say, a StringHolder, what package could I find th...

java - How do I access init parameters from a JSP2 tag file?

I'm trying to create a jsp tag file but it fails to compile when I try to use pageContext.getServletConfig().getInitParameter("myInitParam") I'm using tomcat and when I try to view a page including the file I get a jasper compile error pageContext cannot be resolved. I've also tried just using getInitParameter but it fails also. I can use the request object so I know everything else is fin...

Meaning of serial port parameters in Java?

I'm a beginner in Java. I'm reading data from the serial port. I got serialPort.setSerialPortParams( 9600, SerialPort.DATABITS_8, SerialPort.STOPBITS_1, SerialPort.PARITY_NONE); What is the meaning of 9600, DATABITS_8,STOPBITS_1 and PARITY_NONE?

java - How do I correctly decode unicode parameters passed to a servlet

Suppose I have: &lt;a href="" target="_yahoo" title="Yahoo!&amp;#8482;" onclick="return gateway(this);"&gt;Yahoo!&lt;/a&gt; &lt;script type="text/javascript"&gt; function gateway(lnk) { + '?external_link=' + encodeURIComponent(lnk.href) + '&amp;external_target=' + encodeURIComponent( + '&amp;external_title=' + encodeURIComp...

java - Web Service using Axis 2: Complex type or simple type parameters?

The question is a specialization of: OO style parameters vs type parameters What if you want to define a Web Service operation? To have parameters beeing passed as complex types like this public String insertPerson(Person person); seems pretty cool since you're able to change th...

java - GWT: Capturing URL parameters in GET request

I need to build a GWT application that will be called by an external application with specific URL parameters. For example: How do I capture the orderId parameter inside the GWT application?

Passing several parameters as a single parameter in Java

I've seen an example of it before, but I've never really found any good reference material dealing with it. I know it's possible to pass in several parameters, ints for example, by defining the method as public void aMethod(int...a) But I don't know any more about it than that. I've seen an example, and it returned the average of the ints passed. Is this an out-dated way of passi...

Still can't find your answer? Check out these amazing Java communities for help...

Java Reddit Community | Java Help Reddit Community | Java Community | Java Discord | Java Programmers (Facebook) | Java developers (Facebook)