MySQL Date Changes to Yesterday's date After JPA Save

Question

I have encountered a problem with a date field/database column while using Spring's JpaRepository to save an entity to MySQL.

The entity has LocalDate fields. When testing with LocalDate.now(), encountered a problem with the returned Date field:

First save the returned object's date is correct.
When return the object from MySQL database the date is a day before

Example:

Expected :2019-01-29

Actual :2019-01-28

I have tried it yesterday and results were:

Expected :2019-01-28

Actual :2019-01-27

Maybe similar to the this JPA Saving wrong date in MySQL database

Code

application-mysql-test-connection.properties

spring.jpa.hibernate.ddl-auto=create

# Database url
spring.datasource.url=jdbc:mysql://localhost:3306/test_coupon_system?serverTimezone=UTC

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver

# Test Database credentials
spring.datasource.username=springuser
spring.datasource.password=springuser

### showing values - for development
spring.jpa.show-sql=true

Coupon removed constructor & getter/setter for brevity

@Entity
public class Coupon {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", length = 45)
    private long id;


    @Column(name = "name", unique = true, nullable = false, length = 45)
    private String name;

    @Column(name = "description", length = 100)
    private String description;

    @Column(name = "imageLocation")
    private String imageLocation;

    @Column(name = "startDate", length = 45)
    private LocalDate startDate;

    @Column(name = "endDate", length = 45)
    private LocalDate endDate;

    @ManyToOne(cascade = CascadeType.PERSIST)
    @JoinColumn(name = "company_id")
    private Company company;

    @ManyToMany(cascade = CascadeType.PERSIST,fetch = FetchType.EAGER)
    @JoinTable(
            name = "customer_coupon",
            joinColumns = @JoinColumn(name = "coupon_id"),
            inverseJoinColumns = @JoinColumn(name = "customer_id")
    )
    private List<Customer> customers;

CouponRepository

@Repository
public interface CouponRepository extends JpaRepository<Coupon, Long> {

    Coupon findByName(String name);
}

Test Class with printout

@SpringBootTest
@TestPropertySource(locations = {
        "classpath:application-mysql-test-connection.properties",
})
public class CouponDateIT {

    @Autowired
    private CouponRepository repository;

    @BeforeEach
    void setUp() {
        repository.deleteAll();
    }

    @Test
    void returnsLocalDate() {
        LocalDate testDate = LocalDate.now();

        DateTime.now();
        Coupon coupon = new Coupon();
        String couponName = "test1";
        coupon.setName(couponName);
        coupon.setStartDate(LocalDate.now());
        coupon.setEndDate(LocalDate.now());
        coupon = repository.saveAndFlush(coupon);

        System.out.println("===> test date " + testDate);
        System.out.println("===> coupon from db " + coupon);

        assertEquals(testDate, coupon.getStartDate());
        assertEquals(testDate, coupon.getEndDate());

        Coupon returned = repository.findByName(couponName);
        System.out.println("===> after save " + returned);
        assertEquals(testDate, returned.getStartDate());
        assertEquals(testDate, returned.getEndDate());
    }

Failing Test

Hibernate: select coupon0_.id as id1_1_, coupon0_.company_id as company_7_1_, coupon0_.description as descript2_1_, coupon0_.end_date as end_date3_1_, coupon0_.image_location as image_lo4_1_, coupon0_.name as name5_1_, coupon0_.start_date as start_da6_1_ from coupon coupon0_
Hibernate: select next_val as id_val from hibernate_sequence for update
Hibernate: update hibernate_sequence set next_val= ? where next_val=?
Hibernate: insert into coupon (company_id, description, end_date, image_location, name, start_date, id) values (?, ?, ?, ?, ?, ?, ?)
===> test date 2019-01-29
===> coupon from db Coupon{id=1, name='test1', description='null', imageLocation='null', startDate=2019-01-29, endDate=2019-01-29}
Hibernate: select coupon0_.id as id1_1_, coupon0_.company_id as company_7_1_, coupon0_.description as descript2_1_, coupon0_.end_date as end_date3_1_, coupon0_.image_location as image_lo4_1_, coupon0_.name as name5_1_, coupon0_.start_date as start_da6_1_ from coupon coupon0_ where coupon0_.name=?
Hibernate: select customers0_.coupon_id as coupon_i2_3_0_, customers0_.customer_id as customer1_3_0_, customer1_.id as id1_2_1_, customer1_.email as email2_2_1_, customer1_.name as name3_2_1_ from customer_coupon customers0_ inner join customer customer1_ on customers0_.customer_id=customer1_.id where customers0_.coupon_id=?
===> after save Coupon{id=1, name='test1', description='null', imageLocation='null', startDate=2019-01-28, endDate=2019-01-28}

java.lang.AssertionError: expected:<2019-01-29> but was:<2019-01-28>
Expected :2019-01-29
Actual   :2019-01-28

Log before test with

2019-01-29 11:26:07.986  INFO 6576 --- [           main] g.f.d.s.database.CouponDateIT            : No active profile set, falling back to default profiles: default
2019-01-29 11:26:09.235  INFO 6576 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data repositories in DEFAULT mode.
2019-01-29 11:26:09.308  INFO 6576 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 73ms. Found 3 repository interfaces.
2019-01-29 11:26:09.762  INFO 6576 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration$$EnhancerBySpringCGLIB$$70fe81c1] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2019-01-29 11:26:09.981  INFO 6576 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-01-29 11:26:10.894  INFO 6576 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2019-01-29 11:26:10.957  INFO 6576 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
    name: default
    ...]
2019-01-29 11:26:11.019  INFO 6576 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.3.7.Final}
2019-01-29 11:26:11.019  INFO 6576 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2019-01-29 11:26:11.191  INFO 6576 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.4.Final}
2019-01-29 11:26:11.341  INFO 6576 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
2019-01-29 11:26:11.591  WARN 6576 --- [           main] org.hibernate.mapping.RootClass          : HHH000038: Composite-id class does not override equals(): .entity.Customer_Coupon
2019-01-29 11:26:11.591  WARN 6576 --- [           main] org.hibernate.mapping.RootClass          : HHH000039: Composite-id class does not override hashCode(): .entity.Customer_Coupon
Hibernate: drop table if exists company
Hibernate: drop table if exists coupon
Hibernate: drop table if exists customer
Hibernate: drop table if exists customer_coupon
Hibernate: drop table if exists hibernate_sequence
Hibernate: create table company (id bigint not null, email varchar(45) not null, name varchar(45) not null, password varchar(45) not null, primary key (id)) engine=MyISAM
Hibernate: alter table company add constraint UK_bma9lv19ba3yjwf12a34xord3 unique (email)
Hibernate: alter table company add constraint UK_niu8sfil2gxywcru9ah3r4ec5 unique (name)
Hibernate: create table coupon (id bigint not null, description varchar(100), end_date date, image_location varchar(255), name varchar(45) not null, start_date date, company_id bigint, primary key (id)) engine=MyISAM
Hibernate: create table customer (id integer not null, email varchar(45) not null, name varchar(45) not null, primary key (id)) engine=MyISAM
Hibernate: create table customer_coupon (customer_id bigint not null, coupon_id bigint not null, primary key (customer_id, coupon_id)) engine=MyISAM
Hibernate: create table hibernate_sequence (next_val bigint) engine=MyISAM
Hibernate: insert into hibernate_sequence values ( 1 )
Hibernate: insert into hibernate_sequence values ( 1 )
Hibernate: insert into hibernate_sequence values ( 1 )
Hibernate: insert into hibernate_sequence values ( 1 )
Hibernate: insert into hibernate_sequence values ( 1 )
Hibernate: alter table coupon add constraint UK_dfikvnp7dxdfishfvpnlc0xc1 unique (name)
Hibernate: alter table customer add constraint UK_dwk6cx0afu8bs9o4t536v1j5v unique (email)
Hibernate: alter table customer add constraint UK_crkjmjk1oj8gb6j6t5kt7gcxm unique (name)
Hibernate: alter table coupon add constraint FKe2v6qnb3w90rekqrae28iiqhm foreign key (company_id) references company (id)
Hibernate: alter table customer_coupon add constraint FKppndqdpydmsumc9yqslm5hss4 foreign key (coupon_id) references coupon (id)
Hibernate: alter table customer_coupon add constraint FKi755t5tde9sf6nrp4rm2rnnmn foreign key (customer_id) references customer (id)
2019-01-29 11:26:12.515  INFO 6576 --- [           main] o.h.t.schema.internal.SchemaCreatorImpl  : HHH000476: Executing import script 'ScriptSourceInputFromUrl(/import.sql)'
2019-01-29 11:26:12.515  INFO 6576 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2019-01-29 11:26:12.812  INFO 6576 --- [           main] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
2019-01-29 11:26:13.747  INFO 6576 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2019-01-29 11:26:13.793  WARN 6576 --- [           main] aWebConfiguration$JpaWebMvcConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2019-01-29 11:26:14.592  INFO 6576 --- [           main] o.s.b.a.e.web.EndpointLinksResolver      : Exposing 2 endpoint(s) beneath base path '/actuator'
2019-01-29 11:26:14.655  INFO 6576 --- [           main] g.f.d.s.database.CouponDateIT            : Started CouponDateIT in 6.985 seconds (JVM running for 8.059)

I am expecting the date field not to change after saving and returning it, but the date keeps changing to yesterday's AFTER saving the coupon.

What's the result if you don't use `?serverTimezone=UTC` in the connection string? — x80486, Jan 29 '19 at 10:25
@x80486 without `?serverTimezone=UTC` throws `Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'Jerusalem Standard Time' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.` — George, Jan 29 '19 at 11:07
@AlanHay the value in the database is `2019-01-28`. When save&return by `coupon = repository.saveAndFlush(...)` the date is still `2019-01-29` passing first two assertEquals(...). But, when retrieve from database by `Coupon returned = repository.findByName(...)` the date is changed to `2019-01-28` in both fields and the database. — George, Jan 29 '19 at 12:12
Coupon and the coupon returned by saveAndFlush are the same instance so that is not really telling you anything. The first 2 asserts are against the in-memory model and tells you nothing about the database. Your issue is almost certainly around timezones. — Alan Hay, Jan 29 '19 at 12:32
@AlanHay Thank you I wasn't aware of that. Misunderstood the repository.save()/.saveAndFlush() functions. Thought they return the object from database after save with generated primary key. Tested with different dates and all return a day earlier e.g. saving `2019-02-03` becomes `2019-02-02` in db. Does this help to find the timezone issue ? `SELECT @@GLOBAL.time_zone, @@session.time_zone, @@system_time_zone;` result `SYSTEM SYSTEM Jerusalem Standard Time`. Additionally, used `SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) as GMT_TIME_DIFF;` result `02:00:00` — George, Jan 29 '19 at 13:29
I'm facing the same problem and this solution doesn't work for me. I described what's happening [here](https://stackoverflow.com/questions/54561638/date-issue-in-spring-boot-data-rest). If you guys have some hint, I'll appreciate hearing that. — André Pacheco, Feb 07 '19 at 13:18

score 6 · Answer 1 · answered Mar 28 '20 at 19:48

6

If your timezone is for example

Europe/Warsaw

you can set:

spring.datasource.url=jdbc:mysql://localhost:3306/database?serverTimezone=Europe/Warsaw

instead of this:

spring.datasource.url=jdbc:mysql://localhost:3306/database?serverTimezone=UTC

but you can also add one more entry in application.properties:

spring.jpa.properties.hibernate.jdbc.time_zone=Europe/Warsaw

answered Mar 28 '20 at 19:48

Lukk17s

986
10
11

I had set other properties related to timeZone except spring.jpa.properties.hibernate.jdbc.time_zone and by adding this property now date is being saved in correct timeZone – tyro Jun 10 '20 at 11:33
Setting spring.datasource.url=jdbc:mysql://localhost:3306/database?serverTimezone=Europe/Warsaw for the datasource worked for me and was the perfect solution, since I have multiple datasources, which can have different timezones. Thanks for the solution – Pumpanickel Jul 16 '21 at 10:02

score 5 · Answer 2 · edited Jun 20 '20 at 09:12

Thanks for your help. As noted and answered above, it was a Timezone issue.

Solution:

Set in MySQL SET GLOBAL time_zone = '+02:00';";
Remove ?serverTimezone=UTC from spring.datasource.url=jdbc:mysql://localhost:3306/schema_name

Found these MySQL commands useful: SELECT @@GLOBAL.time_zone, @@session.time_zone, @@system_time_zone; and SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) as GMT_TIME_DIFF;

Additional information Should MySQL have its timezone set to UTC? and in the MySQL Documentaion.

Workaround

As answered above, changing the Spring Boot default mysql-connector-java in pom.xml:

<dependency>
 <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</>
</dependency>

And the driver to spring.datasource.driverClassName=com.mysql.jdbc.Driver in properties file also returned the proper date.

This solution is better than the others because it doesn't target your Java project which already works perfectly with LocalDate / LocalDateTime. Instead it aims to configure the database to set a proper timezone. Since I use docker-compose to boot a development mysql server, I use the `--default-time-zone=America/Sao_Paulo` configuration which solve this issue at the mysql level, not at the Java level. — Rafael Renan Pacheco, Jun 26 '19 at 15:49

score 4 · Accepted Answer · answered Jan 30 '19 at 06:17

I think its because of Timezone problem. Locale Date do not consider the timezone as you already know. But In the database, I guess the date is associated with Timezone. The JPA/Hibernate layer will convert the LocaleDate to TimeStamp( default it will take JVM timezone during the conversion ). The timezone that your running the application is different from the database timezone, due to this there is mismatch.

To confirm on this, set the timezone of the running machine to UTC.

score 4 · Answer 4 · edited Jul 21 '23 at 22:26

4

This issue occured again in Spring Boot 2.2.7 - root cause seems to be bug in MySQL Connector version 8.0.20.

I reported this to Oracle: https://bugs.mysql.com/bug.php?id=99487

Per the bug, the issue can be worked around by setting cacheDefaultTimezone=false.

Alternatively, downgrading to MySQL Connector to 8.0.19 works:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
    <version>8.0.19</version>
</dependency>

edited Jul 21 '23 at 22:26

M. Justin

14,487
7
91
130

answered May 08 '20 at 12:04

estn

1,203
1
12
25

4

solved with: spring.datasource.hikari.data-source-properties.cacheDefaultTimezone=false – estn May 12 '20 at 08:32
I would include the `cacheDefaultTimezone` solution in your actual answer. It's probably a better workaround than downgrading the library, so having it in the answer would be beneficial. – M. Justin Aug 02 '21 at 05:45
I've updated this answer with the actual solution used by the answerer, per their comment. – M. Justin Jul 21 '23 at 22:27

score 3 · Answer 5 · answered Oct 23 '19 at 11:18

In my country, the time is GMT+1 and I had the same issue.

What i did first is serverTimezone=GMT+1 which gives me error.

But JDBC uses a so-called "connection URL", so you can escape "+" by "%2B", that is

db.url=jdbc:mysql://localhost:3306/MyDB?useSSL=false&serverTimezone=GMT%2B1&useLegacyDatetimeCode=false

P.S: check this Why useLegacyDatetimeCode=false to unserstand more.

Hope it helps someone. If it does don't forget to Up Vote this.

working like a charm thanks buddy !!!! – ZINE Mahmoud Jan 16 '22 at 19:56 — ZINE Mahmoud, Jan 16 '22 at 19:56

score 2 · Answer 6 · answered Jan 29 '19 at 15:50

I Have the same problem in Wildfly 13, this is my datasource:

       <datasource jndi-name="java:/comp/env/isobit" pool-name="isobit-dev" enabled="true" use-java-context="true">
                <connection-url>jdbc:mysql://localhost:3306/isobit?zeroDateTimeBehavior=convertToNull&amp;useUnicode=true&amp;useJDBCCompliantTimezoneShift=true&amp;useLegacyDatetimeCode=false&amp;serverTimezone=UTC</connection-url>
                <driver>mysql-connector-java-8.0.13.jar</driver>

Then change the driver to mysql-connector-java-5.1.23-bin.jar and in my standalone.xml

             <datasource jndi-name="java:/comp/env/isobit" pool-name="isobit-dev" enabled="true" use-java-context="true">
                <connection-url>jdbc:mysql://localhost:3306/isobit?zeroDateTimeBehavior=convertToNull</connection-url>
                <driver>mysql-connector-java-5.1.23-bin.jar</driver>
                <security>
                    <user-name>root</user-name>
                    <password>root</password>
                </security>
            </datasource>

Now the dates are the right.

score 1 · Answer 7 · answered Aug 12 '19 at 04:47

1

Convert the Date field to TimeStamp will fix the issue and make sure you pass the timezone through spring properties file

spring.jpa.properties.hibernate.jdbc.time_zone=GMT+10

answered Aug 12 '19 at 04:47

livingobjects

11
3

score 0 · Answer 8 · answered Aug 05 '20 at 19:05

To solve this issue we simply have to set the server timezone to UTC as the timezone differs at every place.

Add or update the following line in application.properties file:

spring.datasource.url=jdbc:mysql://localhost:3306/test_coupon_system?serverTimezone=UTC

score 0 · Answer 9 · answered Jan 26 '21 at 16:01

Timezone mismatch between your Java code which runs on your Local Timezone vs MySQL which is in UTC timezone.

Depending on your timezone this day difference only happens during specific hours when your day is different from UTC.

Change the connection string in MySQL to the same timezone as your computer.

MySQL Date Changes to Yesterday's date After JPA Save

I have encountered a problem with a date field/database column while using Spring's JpaRepository to save an entity to MySQL.

Code

9 Answers9

Solution:

Workaround

Linked