JPA Saving wrong date in MySQL database

Question

I have a table in my MySQL database that has a date column:

+-------------------+---------------+------+-----+---------+----------------+
| Field             | Type          | Null | Key | Default | Extra          |
+-------------------+---------------+------+-----+---------+----------------+
| id                | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| type              | varchar(50)   | NO   |     | NULL    |                |
| expiration        | date          | NO   |     | NULL    |                |

I'm using MySQL with JPA to save dates. I have a feature that the user can select a final date range and it will get all the dates.

Check out this code (with a bunch of SYSOs) to try to see whats going on...

@Override
    protected DateTime nextReference(DateTime reference) {
        System.out.println("Reference: " + reference.toString("dd-MM-YYYY"));
        DateTime plus = reference.plusMonths(1);
        System.out.println("One month from now: " + plus.toString("dd-MM-YYYY"));

        DateTime result = plus.withDayOfMonth(reference.getDayOfMonth());
        System.out.println("Final: " + result.toString("dd-MM-YYYY"));

        return result;
    }

This part, the result was fine:

Reference: 10-01-2017
One month from now: 10-02-2017
Final: 10-02-2017
Reference: 10-02-2017
One month from now: 10-03-2017
Final: 10-03-2017
Reference: 10-03-2017
One month from now: 10-04-2017
Final: 10-04-2017
Reference: 10-04-2017
One month from now: 10-05-2017
Final: 10-05-2017
Reference: 10-05-2017
One month from now: 10-06-2017
Final: 10-06-2017
Reference: 10-06-2017
One month from now: 10-07-2017
Final: 10-07-2017
Reference: 10-07-2017
One month from now: 10-08-2017
Final: 10-08-2017
Reference: 10-08-2017
One month from now: 10-09-2017
Final: 10-09-2017
Reference: 10-09-2017
One month from now: 10-10-2017
Final: 10-10-2017
Reference: 10-10-2017
One month from now: 10-11-2017
Final: 10-11-2017
Reference: 10-11-2017
One month from now: 10-12-2017
Final: 10-12-2017
Reference: 10-12-2017
One month from now: 10-01-2018
Final: 10-01-2018

Ok, now lets move to the save part:

@Transactional
private void saveTransactions(List transactions) {
    for (Transaction t : transactions) {
        System.out.println("Saving: " + t.getExpiration().toString("dd-MM-YYYY"));
        Transaction saved = dao.save(t);
        System.out.println("Saved: " + saved.getExpiration().toString("dd-MM-YYYY"));
    }
}

As you can see, I put some lines to debug this also.... Before I continue with the output, check out the DAO:

public T save(T entity) {
    entityManager.persist(entity);
    return entity;
}

No big deal right... The output:

Saving: 10-02-2017
Saved: 10-02-2017
Saving: 10-03-2017
Saved: 10-03-2017
Saving: 10-04-2017
Saved: 10-04-2017
Saving: 10-05-2017
Saved: 10-05-2017
Saving: 10-06-2017
Saved: 10-06-2017
Saving: 10-07-2017
Saved: 10-07-2017
Saving: 10-08-2017
Saved: 10-08-2017
Saving: 10-09-2017
Saved: 10-09-2017
Saving: 10-10-2017
Saved: 10-10-2017
Saving: 10-11-2017
Saved: 10-11-2017
Saving: 10-12-2017
Saved: 10-12-2017

As you can see... It should be fine right? Everything on the 10th.

Before I continue again, check the model, and the converter:

    //Attribute
    @Convert(converter = JpaDateConverter.class)
    private DateTime expiration;

//Converter

public class JpaDateConverter implements AttributeConverter {

    @Override
    public Date convertToDatabaseColumn(DateTime objectValue) {
        return objectValue == null ? null : new Date(objectValue.getMillis());
    }

    @Override
    public DateTime convertToEntityAttribute(Date dataValue) {
        return dataValue == null ? null : new DateTime(dataValue);
    }

}

Now look at my database:

mysql> select expiration from tb_transaction where notes = 3 and year(expiration
) = 2017;
+------------+
| expiration |
+------------+
| 2017-01-10 |
| 2017-02-10 |
| 2017-03-09 |
| 2017-04-09 |
| 2017-05-09 |
| 2017-06-09 |
| 2017-07-09 |
| 2017-08-09 |
| 2017-09-09 |
| 2017-10-09 |
| 2017-11-10 |
| 2017-12-10 |
+------------+
12 rows in set (0.00 sec)

For some, wierd, mystic reason, some dates where saved on the 9ths instead of the 10th !!

No Warnings, no Errors on MySQL driver or nothing.

Please HELP guys!

EDIT Transaction Class:

@Entity
@Table(name = "tb_transaction")
public class Transaction implements Cloneable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Enumerated(STRING)
    private TransactionType type;

    @Convert(converter = JpaDateConverter.class)
    private DateTime expiration;

HavenNo7 · Accepted Answer

Thanks to @RickS, we solved this issue.

PS: I solved this a long time ago, and experienced this issue again 2 years later. lol

The solution is in the converter: JpaDateConverter, when you convert a DateTime Objetivo to java.sql.Date, according to the docs:

If the given milliseconds value contains time information, the driver will set the time components to the time in the default time zone (the time zone of the Java virtual machine running the application) that corresponds to zero GMT.

So to fix this I changed the server timezone:

dpkg-reconfigure tzdata

And in MySQL:

SET @@global.time_zone = '+00:00';

JPA Saving wrong date in MySQL database

Answers (2)

tl;dr

java.time

`LocalDate`

Date-time math

JDBC 4.2

Date-range

About java.time

Related Questions