HQL to query records between two dates

Question

I am trying to query all customers records using HQL in my Spring/ Hibernate app that have DateAdded between Date1 and Date2 OR LastSeen between Date1 and Date2, so I've build this HQL query in the Repository/ DAO class:

sessionfactory.getCurrentSession().createQuery("from Customer c where c.dateAdded BETWEEN '"+startDate+"' AND '"+endDate+"' OR c.lastSeenDate BETWEEN  '"+startDate+"' AND '"+endDate+"'").list();

I've debugged the app to check the startDate and endDate and found that they are sent as:

startDate: Wed Jan 22 01:16:57 HKT 2014

endDate: Wed Jan 29 01:16:57 HKT 2014

In DB, I am 100% sure there is one record at least meeting this query, as this record DateAdded and LastSeen are as follows:

2014-01-23 15:33:38

2014-01-25 15:33:38

So can someone please tell me what I am doing wrong / missing here?

I'm not sure how time zones are handled in HQL when dates are passed as litterals but you should *really* use query parameters instead of string concatenation. — Guillaume, Jan 21 '14 at 17:37
@Guillaume that's to address this problem specifically or generally speaking? — MChan, Jan 21 '14 at 17:46
@Guillaume changed it to sessionfactory.getCurrentSession().createQuery("from Customer c where c.dateAdded BETWEEN :startD AND :endD").setParameter("startD", startDate).setParameter("endD", startDate).list(); and still it won't work :( — MChan, Jan 21 '14 at 18:04
@Mchan how about `from Customer where dateAdded >= :startD and dateAdded <= :endD` ? I have been using similar syntax and it works fine. If this doesn't work either, then probably there are other problem. — Adrian Shum, Jan 22 '14 at 04:06
@AdrianShum Tried it, I tried passing the dates formatted just as in the DB but it won't accept as the parameters are expecting Date while I am passing a string. — MChan, Jan 22 '14 at 10:14
Could it be a time zone issue? What are the TZ of the client and the server? TZ handling in JDBC is driver specific, you might want to look at this question http://stackoverflow.com/questions/9202857/timezones-in-sql-date-vs-java-sql-date — Guillaume, Jan 22 '14 at 12:01
@Guillaume The server and user browser are both in Hong Kong, but this will raise another major concern. What if we moved the app from HK to USA or new users from different locations worldwide started using the app? Will I need to code differently for each individual time zone?! Isn't there anyway to overcome the timezones problem? — MChan, Jan 22 '14 at 12:09
@Guillaume I made a research and found that some developers recommend always saving dates on server in GMT format, then converting it on users browsers depending on the user time zone...they also mentioned that there are frameworks that handle this, but I couldn't find any, any thoughts where I can find such Java based framework? — MChan, Jan 22 '14 at 12:12
@Guillaume your reply seems to be the only realistic one so it might be a good idea to post it as an answer so that I can accept it — MChan, Jan 22 '14 at 14:37
@MChan: No you shouldn't pass as string. `Date`s are passed as is a date. Therefore you shouldn't need to care anything about the date formatting. — Adrian Shum, Jan 23 '14 at 03:01
For date/time handling in global system, there are already plenty of discussion here in SO, which is in fact off topic from this question. http://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices — Adrian Shum, Jan 23 '14 at 03:03

Ashish Jagtap · Answer 1 · 2014-01-22T11:06:49.887

21

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String frmDate = format.parse(startDate);
String enDate = format.parse(endDate);
sessionfactory.getCurrentSession()
.createQuery("FROM Customer AS c WHERE c.dateAdded BETWEEN :stDate AND :edDate ")
.setParameter("stDate", frmDate)
.setParameter("edDate", enDate)
.list();

hope this will help!

edited Jan 22 '14 at 11:06

answered Jan 22 '14 at 07:52

Ashish Jagtap

2,799
3
30
45

This won't work as the moment you return the formatted dates back to Date objects all format is gone – MChan Jan 22 '14 at 10:26
1

This also won't work :( because setParameter will check the passed variable type. So it will raise an exception of failure to cast between String and Date. Even if you tried to cast in setParameter it won't work – MChan Jan 22 '14 at 12:06
use old style as you write in question – Ashish Jagtap Jan 22 '14 at 12:19
1

Yes for sure this is the most appropriate solution for now, but that's not the best as it makes code prone to SQL injection, setting parameters is considered one of the best practices to avoid security breaches and making sure code is type safe – MChan Jan 22 '14 at 12:22

score 6 · Answer 2 · answered Dec 15 '14 at 18:47

This is an old post, but I figured it might help someone. Setting .setTimeStamp should do the trick.

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String frmDate = format.parse(startDate);
String enDate = format.parse(endDate); 
sessionfactory.getCurrentSession()
.createQuery("FROM Customer AS c WHERE c.dateAdded BETWEEN :stDate AND :edDate ")
.setTimestamp("stDate", frmDate)
.setTimestamp("edDate", enDate)
.list();

score 2 · Answer 3 · edited Jul 28 '15 at 13:09

2

SimpleDateFormat sf=new SimpleDateFormat("dd-MM-YYYY");
String fromDate=null;
String toDate=null;
fromDate=sf.format(startDate);
toDate=sf.format(endDate);
sessionfactory.getCurrentSession().createQuery("from Customer where dateAdded BETWEEN '"+startDate+"' AND '"+endDate+"'");

edited Jul 28 '15 at 13:09

Pierre-Alexandre Moller

2,354
1
20
29

answered Jul 28 '15 at 12:12

Altin Vijay

29
2

1

You should explain your answer rather than just post code. – Martin Jul 28 '15 at 12:42

score 1 · Answer 4 · answered Jan 24 '18 at 06:31

SimpleDateFormat formatter = new SimpleDateFormat("MM-dd-yyyy");
    Calendar c = Calendar.getInstance();
    c.setTime(new Date()); // Now use today date.
    c.add(Calendar.DATE, 90);
    Date fromDate = null, toDate = null;
    String fromDateStr = formatter.format(new Date());
    String toDateStr = formatter.format(c.getTime());
    try {
        fromDate = formatter.parse(fromDateStr);
        toDate = formatter.parse(toDateStr);
    } catch (ParseException e) {
        e.printStackTrace();
    }
    query.setParameter("stDate", fromDate);
    query.setParameter("edDate", toDate);

score 1 · Answer 5 · edited May 11 '18 at 12:42

It's important to know that when using BETWEEN “setDate” will truncate the HQL date value passed as parameter and ignore the hours, minutes, seconds. This is very important to note especially if you have an HQL query checking between dates from different days because using “setDate” will interpret the date interval as between midnight of the specified dates.

The solution is to use “setParameter” instead of “setDate” which causes the HQL date values to be interpreted as dates and times.

here's a few examples in my site for those interested http://www.coding-dude.com/wp/java/hql-date-datetime-quick-tip/

You need to add a disclaimer if you are linking to your site. — Suraj Rao, May 11 '18 at 12:41

MR AND · Answer 6 · 2017-12-04T17:43:56.730

0

I had similar issue. When we use end date (Wed Jan 29), the system looks for Wed Jan 29 00:00:00, which is just like choosing Jan 28. In order to avoid such case, we can add one day to the end date. This also explains why we do not have issue with the start date.

Surprisingly, this issue does not exist when we use hibernate criteria.

edited Dec 04 '17 at 17:43

answered Dec 04 '17 at 17:37

MR AND

376
7
29

score 0 · Answer 7 · answered Sep 28 '18 at 14:26

You need to annotate with @JsonFormat the same way the date filters and date field as follows:

Class Filter {
...
@JsonFormat(shape=JsonFormat.Shape.STRING, pattern="dd/MM/yyyy HH:mm", timezone="America/Sao_Paulo")
private Date startDate;
}

Class YourObject {
...

@JsonFormat(shape=JsonFormat.Shape.STRING, pattern="dd/MM/yyyy HH:mm", timezone="America/Sao_Paulo")
private Date DateAdded;
}

The pattern and timezone should be adjusted to your region.

score -1 · Answer 8 · answered Jan 22 '14 at 01:27

-1

Try something like this:

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date fromDate = df.parse(startDate);
Date toDate = df.parse(endDate);
Criteria criteria = sessionfactory.getCurrentSession().createCriteria(Customer.class)
   .add(Restrictions.between("dateAdded", fromDate , toDate ));
List<Customer> listCustomer = criteria.list();

Hope this helps..!!

answered Jan 22 '14 at 01:27

Mukesh S

2,856
17
23

2

This won't work as the moment you return the formatted dates back to Date objects all format is gone – MChan Jan 22 '14 at 10:23

HQL to query records between two dates

8 Answers8

Linked