Spring REST LocalDate UTC differs of one day

Question

I'm using Spring Boot 1.5.4, Hibernate 5.2.10, Spring Data REST, HATEOAS, JDK8 with LocalDate and LocalDateTime. My computer is on CEST timezone but I want the application works in UTC, so I set in application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/database?useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=true
spring.jpa.hibernate.jdbc.time_zone = UTC

According to this article I don't want to change the timezone of my JVM because seems not to be a best practice.

Spring Data REST exposes my repositories and I use Swagger2 to have a nice interface to use API. When I try a endpoint I see something like:

    {
  "_embedded": {
    "dailyCodes": [
      {
        "sid": "d495cdaa-14f2-44cb-a98f-8aa6ddd43d91",
        "createdDate": "2017-06-28T16:20:01",
        "lastModifiedDate": "2017-06-28T16:20:01",
        "lastModifiedBy": "admin",
        "date": "2017-06-28",
        "code": "js",
        "new": false,
        "_links": {
          "self": {
            "href": "http://localhost:8080/api/v1/dailyCodes/1"
          },
          "dailyCode": {
            "href": "http://localhost:8080/api/v1/dailyCodes/1"
          }
        }
      }

Like you can see the datetime format is fine and also it display the CEST time even if in the db the real time is 14:20:01. I guess this is wrong because my REST API should work in UTC.

How could I achieve this result?

Always on the same topic, I've a REST endpoint (exposed by Spring Data REST) to search using LocalDate params; I'm using

@Transactional
@PreAuthorize("isAuthenticated()")
public interface DailyCodeRepository extends PagingAndSortingRepository<DailyCode, Long> {

@Query("SELECT d FROM DailyCode d WHERE (:code IS NULL or code=:code) AND (:from IS NULL OR date>=:from) AND (:to IS NULL OR date<=:to)")
    public Page<DailyCode> findAllWithParameter(@Param("code") @RequestParam(value = "code", required = false) String code,
            @Param("from") @RequestParam(value = "from", required = false) LocalDate from,
            @Param("to") @RequestParam(value = "to", required = false) LocalDate to, Pageable pageable);
}

Also in this case I've a strange thing: if I call the endpoint passing parameters, in the server they arrive with 1 day less. Furthemore the date pattern accepted seems to be the JDK default of my locale (Italian) but I guess it's not a best practice.

Is there a best practice to follow to avoid any problems with date/time arguments in both direction?

Solution

I agree with the author of this article The 5 laws of API dates and times that we have to store and return time in UTC. And 'front-end' must decide itself how to convert the time value depending on the client time-zone.

To achieve this (store and return time in UTC) we set JVM parameter -Duser.timezone=UTC or add spring.jpa.properties.hibernate.jdbc.time_zone=UTC to application.properties (starting from Hibernate 5.2.3.Final). Then change time fields of our entities from LocalDateTime (that doesn't store time-zone info) to ZonedDateTime type.

After that the time values will be stored in UTC independently from the local time-zone of the computer where the application is started and SDR will return these values in ISO8601 form: 2017-07-02T11:58:10.089Z

But if it's necessary to return time values in the specific time-zone we have to setup @JsonFormat annotation to all of our time fields:

@JsonFormat(timezone = "Europe/Rome", pattern = "yyyy-MM-dd'T'HH:mm:ss.SSSZ")
private ZonedDateTime createdDate;

Our we can define constant TIME_ZONE in our application and set it in @JsonFormat annotations: @JsonFormat(timezone = Application.TIME_ZONE, ...).

Then we get this output:

{
    //...
    "createdDate": "2017-07-02T14:11:45.964+0200",
    //...
}

Unfortunately, parameter spring.jackson.time-zone (based on my short investigation) affects only on service messages in the application, for example:

{
    "timestamp": "2017-07-02T14:14:09.486+0200",
    "status": 404,
    "error": "Not Found",
    "message": "No message available",
    "path": "/users"
}

To get time here in 'zoned' form we have to set properly spring.jackson.date-format parameter in the application.properties:

spring.jackson.date-format=com.fasterxml.jackson.databind.util.StdDateFormat

Comments

[rmalchow]

I'm not very familiar with LocalDate and such. However, the jdbc.time_zone parameter only affects the conversion between inside the VM and the database. In the JDBC driver, there will be a Date object, which is basically era milliseconds without any timezone information. usually, when this is formatted as a (human readable) string, this would be done according to the timezone specified in the formatter OR the default timezone.

[rmalchow]

however, when it is formatted to be written to the database as a date, the jdbc.time_zone parameter is used for this conversion. i am not sure what happens when that Date object is converted to a LocalDate, but i am assuming that the problem you have lies there, rather than in the conversion between VM and DB.

[drenda]

I'm absolutely sure. In the DB the time is in UTC, but when I return the value using Spring REST the date is in my local time zone.

[Cepr0]

I'm about that: spring.jpa.hibernate.jdbc.time_zone = UTC in your question. In my answer I suggest to use another option: 'spring.jackson.time-zone'

[drenda]

Yes I'm sure. This is a portion on my properties file: #Jackson spring.jackson.mapper.infer-property-mutators=false spring.jackson.time-zone=UTC

[drenda]

Are your JDK in a different timezone from UTC? Are you storing date in UTC format in the db?

[Cepr0]

@drenda It doesn't matter now... I was misled by the time value in an error message. See my answer I updated it...

[drenda]

congratulation for the reply.Using ZonedDateTime in combination with @JsonFormat(timezone = "UTC", pattern = "yyyy-MM-dd'T'HH:mm:ss.SSSZ") I've UTC reply in REST response. I'm wondering if there is a way to avoid use ZonedDateTime as entity field, or at least set a global JsonFormat. I've to set the annotation on all beans! Thanks