Hibernate query returning wrong date

Question

When i retrieve a date from a mysql table it always return the date 1 day before, the date format is yyyy-MM-dd, so if i have 2018-10-17 when i print the result it comes as 2018-10-16. If i use the command line to check the dates, they are correct. This is the method inside the Dao class that retrieves the dates:

public List<LocalDate> getDates(String username){
    List<LocalDate> dates;
    Query query = manager.createQuery("select date from Calendar where username =:username");
    query.setParameter("username",username);
    dates = query.getResultList();
    return dates;
}

When I print the list all the dates are printed 1 day before as I was on my sql.

This is how I get the dates on the servlet and use hibernate to store the dates on mysql.

String dateString = request.getParameter("date");
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
LocalDate dataInicial = LocalDate.parse(dataInicialString, formatter);

The Datatype that im using in mysql is DATE. Then I user hibernate to do the rest for me, if you need any piece of code that I didin't provide please ask me. Thx in advance.

Sorry for bad english!

The classes Date and SimpleDateFormat are outdated and poorly designed. A newer Hibernate should be able to get you the date as LocalDate from the database, which I warmly recommend. It solves your problem since a LocalDate has got neither time of day nor time zone. As an extra bonus LocalDate and the other classes from java.time are so much nicer to work with. java,time is the modern Java date and time API. — Anonymous
– Anonymous, Commented Oct 8, 2018 at 18:46
If you can't change the server's timezone settings, you can try changing Hibernate's settings as described by one of it's maintainers vladmihalcea.com/… — coladict
– coladict, Commented Oct 9, 2018 at 7:58
@OleV.V. i made some changes to the code and updated the question/post, i think that should be it, but it still remains the same problem, i checked the date before storing it in mysql ant it was the date that i wnated, i used the command line to select the dates in mysql and there they were correct too, im using the Datatype in mysql as DATE, should i change it? or is it still some code errors to fix? thx for your time and answer. Sorry for bad english if any mistakes were made — Galdeano
– Galdeano, Commented Oct 9, 2018 at 13:08
Sorry that it didn’t help as expected. I’m afraid I’m out of suggestions, but is you tell us the datatype in MySQL, someone may more easily help you? — Anonymous
– Anonymous, Commented Oct 9, 2018 at 13:11
@OleV.V. i found what needed to be done, i changed the HQL query, i dont know exactly why but when i do "Query query = manager.createQuery("select DATE_FORMAT(date,'%Y-%m-%d') from Calendar where username =:username");" it works!!!! And thx for the LocalDate advice, it is nicer to work with! — Galdeano
– Galdeano, Commented Oct 9, 2018 at 14:18

Galdeano · Accepted Answer · 2018-10-09 14:19:08Z

1

i found what needed to be done, i changed the HQL query, i dont know exactly why but when i do

Query query = manager.createQuery("select DATE_FORMAT(date,'%Y-%m-%d') from Calendar where username =:username");

it works!!!!

answered Oct 9, 2018 at 14:19

Galdeano

316 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KJEjava48 Over a year ago

I am also facing exactly the same that u had, but in my case i have to retrieve the whole row instead of only date in select query, What should i do??? Please help me...

Alain Cruz · Accepted Answer · 2018-10-08 18:18:13Z

0

I once had this issue and the reason resided in the default time zone of my MySQL configuration. So, first check out the time zone you are using with the following query.

SELECT @@global.time_zone;

If it is not the desired time zone, try going into your MySQL configuration file and add the following line.

[mysqld]
default-time-zone = '00:00'

Remember to set it to the desired time zone. You will need to restart your MySQL for the change to take place. This change will be permanent.

answered Oct 8, 2018 at 18:18

Alain Cruz

5,1473 gold badges30 silver badges47 bronze badges

1 Comment

KJEjava48 Over a year ago

SELECT @@global.time_zone return SYSTEM, what to do??

Collectives™ on Stack Overflow

Hibernate query returning wrong date

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related