Wrong date selection in SQL

Question

I'm working with Hibernate 4.3.8.Final, Primefaces 6.0 and MySQL Database 5.7.13. I have a table in the database with this structure:

CREATE TABLE `rents` ( 
`rent_code` INT NOT NULL AUTO_INCREMENT,
`rent_daystart` datetime default NULL,
`rent_dayend` datetime default NULL,
PRIMARY KEY  (`rent_code`)
) ENGINE = innodb CHARACTER SET utf8 COLLATE utf8_unicode_ci;

And the following data extracted with Squirrel with the following SQL:

select * from rents

rent_code | rent_daystart         | rent_dayend
1         | 2016-11-30 16:03:00.0 | 2016-12-01 16:03:00.0

In my Java bean I have the following function:

public List<Object> getRents(java.util.Date iniDate, java.util.Date endDate){
        String SQL="select rent_code from rents where rent_daystart < :inidate and rent_dayend > :enddate"; 
        List<Object> allRecords = null;
        Session sesion=HibernateUtil.getSessionFactory().openSession(); 
        try {       
             sesion.beginTransaction();
             Query query = sesion.createSQLQuery(SQL).setDate("inidate", iniDate).setDate("enddate", endDate);  
             allRecords = query.list();
             sesion.getTransaction().commit();
             sesion.close();                                  
         }
          catch (HibernateException he) {
             //exception control code
          };
          return allRecords;
}

I execute the web APP debugging and the dates that the function receives are:

**inidate** = 'Wed Nov 30 17:54:00 CET 2016'

**enddate** = 'Wed Nov 30 18:54:00 CET 2016'

And it returns NO RECORD AT ALL.

If I execute the same SQL in squirrel that way:

select rent_code from rents where rent_daystart < '2016-11-30 17:54:00' and rent_dayend > '2016-11-30 18:54:00'

It returns one record.

I suspect that this is a data type problem or something like that, but after researching in the web it is not clear to me.

May someone help me?

Thanks in advance!

If you are using a logging framework, try turning on Hibernate [SQL-level logging](http://stackoverflow.com/a/2536835/1255737) to make sure its actually performing the query you think it is. — rmlan, Nov 30 '16 at 18:19

score 0 · Answer 1 · answered Nov 30 '16 at 18:10

0

The java.util.Date class doesn't handle time zones. Unless you are using the same time zone in both your Date instance and the database fields, you can easily mismatch the time zones creating the behavior that you are seeing. Try and display the java Date in UTC format, and look at your database date in UTC format, and see if they line up like you expect.

answered Nov 30 '16 at 18:10

Jeremy Gurr

1,613
8
11

I had never dealed with date timezones or simillar and I'm a bit overflowed because all the data I have to asimilate. After reading some documentation ( https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html ) my first question is: If I have correctly understood DATETIME in MySQL doesn't store timezone, when retrieving such values is some timezone added? In case the value is recovered "as is" to make a perfect comparison between them should I simply format the date as amishra says? – Jesus Schneider Nov 30 '16 at 21:57
Here's where you can read about mysql timezones: http://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html – Jeremy Gurr Dec 01 '16 at 02:53
Since you pass in a Date object and not a string, formatting at that point is irrelevant. Just make sure that the date you are passing in is correct. Since you haven't shown us the code you use to pass in the date to the method, we can't tell you if it's right or not. – Jeremy Gurr Dec 01 '16 at 02:55

score 0 · Answer 2 · answered Nov 30 '16 at 18:43

If it is a date format mismatch between the values passed from the code and the values expected in database then use SimpleDateFormat to change the format of the date values before passing on to databse

 SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
 Date date = dt.parse(iniDate);

score 0 · Answer 3 · edited May 23 '17 at 11:45

0

Well, I have found the problem. As stated in Compare Date Java to DateTime Column in database table using SQL I was using the ".setDate" function to set the dates, such function truncates the time part, that was the problem. Now I'm using the ".setTimestamp" that uses both, date and time parts. Thanks to all for your help, I have learned a lot about TimeZones!

edited May 23 '17 at 11:45

Community

1
1

answered Dec 01 '16 at 14:06

Jesus Schneider

103
1
11

Wrong date selection in SQL

3 Answers3