How does spring.jpa.properties.hibernate.jdbc.time_zone work internaly?

[Puspender Tanwar]

I have spring.jpa.properties.hibernate.jdbc.time_zone = UTC set into my project's application.properties file and it's also working fine. I am into IST timezond and when I POST data to my REST API, the UTC gets saved into the database. And when I fetch the data from my REST API, I can see the UTC being converted to `IST` automatically.

How does this happen internally? Does anything depend on the system's JVM timezone? What would happen when I deploy my REST API and the Database to AWS instance?
I am worried if this would work if I go into production. As of now, everything is local to my system.

Guidance, please.

[Stephan van Hulst]

It depends on how you handle dates during transit through your service. There's a lot that can happen between retrieval from the database and response to the client. Please show us how you know that the time is converted to IST after you've retrieved it.

[Puspender Tanwar]

This is the Comment entity:

 
public class Comment {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "native")
    @GenericGenerator(name = "native", strategy = "native")
    @Column(name = "id", unique = true, nullable = false)
    private Long commentId;


    private String content;

    @Column(updatable = false, nullable = false)
    @CreationTimestamp
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    private LocalDateTime createdAt;

    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @UpdateTimestamp
    private LocalDateTime updatedAt;

}

POST operation:

@PostMapping
public Comment create(@RequestBody @Valid Comment comment) {

    return commentService.create(comment);
}

//using spring-data-jpa
public interface CommentRepository extends JpaRepository<Comment, Long> {}

//Service
public Comment create(Comment comment){

   return commentRepository.save(comment);
}

GET operation : Repository, controller and service methods:

@GetMapping(value = "{commentId}")
 public Comment find(@PathVariable Long commentId){

     return commentService.find(commentId);
}

public Comment find(Long commentId) {
        return commentRepository.findById(commentId).orElseThrow(() -> new ResourceNotFoundException("Comment not found"));    
}

This is all I am doing apart from setting spring.jpa.properties.hibernate.jdbc.time_zone = UTC into the application.properties file. I have done nothing manually to change or manipulate the timezones.

So, I commented on 2020-05-02 06:49:53 and the createdAt time got saved into DB was 2020-05-02 01:19:53. Then I retrieved the comment and the createdAt recieved was 2020-05-02T06:49:53 which is rightly converted IST timezone which is UTC+5:30


{
 "commentId": 110,
 "content": "comment at 6:49:55",
 "createdAt": "2020-05-02T06:49:53",
 "updatedAt": "2020-05-02T06:49:53",
}

[Stephan van Hulst]

There are two places where the conversion can take place: Either Jackson does it when de(serializing) JSON, or Hibernate does it when storing/retrieving the date and time. You can find out which of the two does it by printing the value of comment.updatedAt before you save it in the repository and after you retrieve it from the repository.

It's likely Hibernate. Hibernate sets the values of your timestamps fields to the local time of the JVM where the application runs, and then performs the conversion when sending and receiving the values from the database. This will work fine as long as you use @CreationTimestamp and @UpdateTimestamp.

Problems may occur if you allow the client to set fields of type LocalDate, LocalTime and LocalDateTime. In general it's not a good idea to use the same class to both represent data in your database and data received from the client. For instance, what if I POST your JSON example from Germany? First of all, why can the client POST commentId, createdAt and updatedAt at all? Those fields should be determined by the server, not the client. Secondly, will the dates and times that your server receives be interpreted as IST, as CET or as UTC?

Don't allow the client to send timestamps to the server. For dates and times that the client MAY send, such as when setting a user's birth date, make sure that the client sends the data with a timezone or offset specifier.

[Puspender Tanwar]

Problems may occur if you allow the client to set fields of type LocalDate, LocalTime and LocalDateTime. In general it's not a good idea to use the same class to both represent data in your database and data received from the client. For instance, what if I POST your JSON example from Germany? First of all, why can the client POST commentId, createdAt and updatedAt at all? Those fields should be determined by the server, not the client

I have separate IN and OUT Dtos which uses mapping library MapStruct to map the IN and OUT fields. The createdAt and updatedAt are not sent by the client, those are set by the server. I removed all these details to avoid verbosity. I kept the bare minimum code.

You can find out which of the two does it by printing the value of comment.updatedAt before you save it in the repository and after you retrieve it from the repository

Yes I will check this out and post the results here.

Thanks for your time Stephan

[Puspender Tanwar]

So I tested posting the new entity at 2020-05-27 14:15:20 UTC:

    @PrePersist
    public void printDate(){
        System.out.println("Before persist: "+this.createdAt);
    }

    public LocalDateTime getCreatedAt() {
        System.out.println("getter: " + this.createdAt);
        return createdAt;
    }

Output:
Before persist: null
getter: 2020-05-27T19:45:20

Means, it is done by the Hibernate using the JVM's default timezone.