Timezone issue in Spring Boot JPA application

Question

I am facing date time issue in my spring boot jpa application.

For example, In my database I have one column created_on which contains 2019-07-11 09:30:00 date. When I fetch this record threw JPA it converts to UTC.
Means date 2019-07-11 09:30:00 converts to 2019-07-11 05:00:00.

My System time is in IST and date is saved in database in IST as well.

I am using mysql database.

In my Enitity

private Date createdOn;

Database column:

created_on timestamp

Service:

@Service
@Transactional(readOnly = true)
public class EntityTypeService {
  @Autowired
  private IEntityTypeRepository entityTypeRepository;

  public EntityType findById(Long id) {
    EntityType entityType = entityTypeRepository.findById(id).orElse(new EntityType());
    System.out.println(entityType.getCreatedOn());
    return entityType;
  }
}

Repository

@Repository
public interface IEntityTypeRepository extends CrudRepository<EntityType, Long> {
}

Date in database is 2019-07-11 09:30:00

But when I print it on service System.out.println(entityType.getCreatedOn()); it gives me 2019-07-11 05:00:00.

This is generic issue in my whole application.

And the problem is? Please show a [minimal-reproducible-example](https://stackoverflow.com/help/minimal-reproducible-example) — Luuk, Jul 30 '20 at 05:57
Could you add code how you save and read your date object? Do you use LocalDateTime for both? — Milgo, Jul 30 '20 at 05:58
@Luuk this is generic issue in my whole application that's why i have not attached any code previously. After you suggestion added code. Please let me know anything need. — Vivek Jain, Jul 30 '20 at 06:12
@Milgo I have date already available in database which is correct by when i fetch it using `findById` method of `CrudRepository` it gives me converted date to UTC. Attached code for reference. — Vivek Jain, Jul 30 '20 at 06:14

Vivek Jain · Accepted Answer · 2020-08-07T05:36:53.053

After so much research I found the solution.

Actually issue was because of

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

this property which I have set in my appication.properties.

When I removed this property everything works fine at the backend. Backend showing the perfect time which is available in database.

But now when response comes to frontend, at that time my date gets converted to UTC.

Like backend it's showing 2020-06-03 18:56:14.0 and when it comes to front end it converts to 2020-06-02T13:26:14.000+0000.

Which is also an issue for me.

So after some more research I found that Jackson by default converts all date to UTC when object send to frontend.

The solution to this problem is

spring.jackson.time-zone=IST

My Database and System timezone is IST so I have set IST to jackson timezone too which solves the problem.

Hope this answer may help someone.

Vivek Jain, you don't have to remove the jpa property, instead add `serverTimezone` query parameter. See my answer above and as well as @Eklavya answer. — vijay, May 04 '21 at 19:01

Eklavya · Answer 2 · 2020-07-30T08:39:36.530

2

You can set timezone for in database connection using serverTimezone eg:Asia/Kolkata and use useLegacyDatetimeCode=false

spring.datasource.url= = jdbc:mysql://127.0.0.1/db_name?useLegacyDatetimeCode=false&serverTimezone=Asia/Kolkata

And don't use legacy class Date rather use modern class LocalDateTime

edited Jul 30 '20 at 08:39

answered Jul 30 '20 at 08:32

Eklavya

17,618
4
28
57

Tried this but no luck. – Vivek Jain Jul 30 '20 at 08:35
Are you sure your timezone in your application are same as application ? And don't use legacy class Date rather use LocalDateTime – Eklavya Jul 30 '20 at 08:38
yes my system time and mysql time is same. also i have printed `System.out.println(new Date());` which gives me perfect time as my system has. – Vivek Jain Jul 30 '20 at 08:42
1

Use `LocalDateTime` class `Date` is legacy class with troublesome behavior – Eklavya Jul 30 '20 at 08:45
I have given as example. But currently i am using native query in JPA which returns me Map there also date gets converted. – Vivek Jain Jul 30 '20 at 08:47
If you use in entity `LocalDateTime` for column it will return `LocalDateTime` when you are doing native query also – Eklavya Jul 30 '20 at 08:49
Suppose I don't want to convert result into Entity. I just want to return `Map` as a result which was returning from JPA NativeQuery then? – Vivek Jain Jul 30 '20 at 08:52
That's different problem I think, here from object you want to cast the data if I properly understand. And get as Object then cast is not a good way I think. And you current problem in post will be solved using this. And cast as LocalDateTime should work I think but not tested – Eklavya Jul 30 '20 at 09:00

vijay · Answer 3 · 2021-05-04T19:06:49.900

When working on JPA + Spring Boot for a backend application, make sure to use same TimeZone through out server configuration. For example, to use UTC do as below:

On server OS level: sudo timedatectl set-timezone UTC (for example on Cent OS)
On MySQL / Database server in my.cnf file (optional)
Spring config: spring.jpa.properties.hibernate.jdbc.time_zone=UTC
MySQL URL query parameters spring.datasource.url=jdbc\:mysql\://localhost\:3306/my_db_name?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useLegacyDatetimeCode=false
And if you are using Jackson JSON framework you can automatically parse DateTime value to String format using @JsonFormat(shape= JsonFormat.Shape.STRING, pattern="dd MMM yyyy HH:mm:ss") on the entity property / field.
Application entry point @SpringBootApplication

@PostConstruct public void init(){ TimeZone.setDefault(TimeZone.getTimeZone("UTC")); }

This should cover most of the implementation challenges while work on Dates and Timestamps. Hope that it helps.

Sir, the only thing that helped in my case (Sping + MySQL 8) is setting context-wide TimeZone in the configuration class; thanks! — Anatoly Yakimchuk, Dec 08 '22 at 22:17

score 0 · Answer 4 · answered Jul 30 '20 at 06:46

0

Try to avoid using Date for timestamps in time zones because it is not designed to handle these in a good way.

You could try to print the date object with a formatter or use LocalDateTime (or ZonedDateTime if time zones are important to you) instead.

answered Jul 30 '20 at 06:46

Milgo

2,617
4
22
37

I don't think this is an issue. Because if i use native query in JPA which returns me Map there also date gets converted. – Vivek Jain Jul 30 '20 at 07:04

score 0 · Answer 5 · edited Aug 01 '20 at 10:10

0

You may try putting @DateTimeFormat(pattern = "yyyy-MM-dd'T'HH:mm:ss.SSSX") on createdOn setter.

edited Aug 01 '20 at 10:10

Ambroise Rabier

3,636
3
27
38

answered Aug 01 '20 at 09:47

bhuwan

116
5

Timezone issue in Spring Boot JPA application

5 Answers5

Linked