Select returning dates in the object other than what is registered in the Mysql database

Question

I have a problem when I list the contents of a table and store in a list the date is one day less.

I’ve done a lot of research on the web and can’t find a solution to the problem.

Below the method of registration:

public String cadastrarPeriodo(LocalDate dataInicio, LocalDate dataFim) throws SQLException {
    String message = null;

    Connection con = ConexaoMySQL.getConexaoMySQL();
    PreparedStatement stmt = null;

    try {
        stmt = con.prepareStatement(inserirPeriodoBancoHora);
        stmt.setDate(1, Date.valueOf(dataInicio));
        stmt.setDate(2, Date.valueOf(dataFim));

        int res = stmt.executeUpdate();

        if(res == 1){
            message = "Dados Registrados com Sucesso!";
        } 
    } catch (Exception e) {
        e.printStackTrace();
        message = "Erro ao Salvar os Dados";
    } finally {
        stmt.close();
        con.close();
    }

    return message;
}

Search method

public List<PeriodoBancoHora> listarPeriodosCadastrados() throws SQLException {
    List<PeriodoBancoHora> listaPeriodoBancoHora = new ArrayList<PeriodoBancoHora>();

    Connection con = ConexaoMySQL.getConexaoMySQL();
    PreparedStatement stmt = null;

    try {
        stmt = con.prepareStatement(listarPeriodosCadastrados);

        ResultSet res = stmt.executeQuery();

        if(res.next()){
            do {
                PeriodoBancoHora periodoBancoHora = new PeriodoBancoHora();
                periodoBancoHora.setIdPeriodoBancoHora(res.getInt(1));
                periodoBancoHora.setDataInicio(res.getDate(2).toLocalDate());
                periodoBancoHora.setDataFim(res.getDate(3).toLocalDate());
                listaPeriodoBancoHora.add(periodoBancoHora);
            } while (res.next());
        } 
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        stmt.close();
        con.close();
    }

    return listaPeriodoBancoHora;
}

In the database is recording the value I am reporting on the screen, however the value returned is subtracting 1 day, as the images below:

The Mysql connector I’m using is mysql-Connector-java-6.0.6.jar.

Thanks in advance.

Sincerely yours,

Answer 1

Thanks for the feedback, I managed to solve the problem by adding up the tips received here. First the indication of Krismorte, led me to understand that my problem was connected to Timezone, I researched how to adjust Timezone in the database, other ways to apply the suggested configuration, but nothing worked. After rLinhares indicated the update of the connection driver, I did and the problem persisted, that’s when I researched how to enter with Timezone directly in the connection string of the bank and to my surprise, there was already a configuration of Timezone=UTC that I changed to Timezone=America Sao_paulo.

And started returning the value correctly.

Thank you for everyone’s support and there are options for those facing similar problems.

Answer 2

replaces that line:

res.getDate(2).toLocalDate()

for:

res.getDate(2).toInstant().atZone(ZoneId.systemDefault()).toLocalDate();

https://stackoverflow.com/a/21242111/4722804