ZonedDateTime in json column of database table, when fetched, it changed to UTC

Question

I am developing a spring-boot project with MariaDB database.

I have a Student entity, which has a Exam property. In database, I would like to store the Exam property as a json field/column in student table.

The SQL for creating student table is:

CREATE TABLE IF NOT EXISTS student (
    id bigint not null,
    exam json,
    first_name varchar(255),
    last_name varchar(255),
    primary key (id)
    ) engine=InnoDB;

The Student entity class is:

@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@TypeDefs({
    @TypeDef(name = "json", typeClass = JsonStringType.class)
})
public class Student implements Serializable {
   ...

  @Column(columnDefinition = "LONGTEXT")
  @Type(type = "json")
  private Exam exam;
}

Those annotations you see above are meant to store the exam property as a JSON field in student table.

The Exam class is not annotated as a JPA entity, but a normal class:

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Exam implements Serializable {
    private ZonedDateTime startTime;
    private String code;
}

As you see, the Exam has a ZonedDateTime property. My question later is about it.

I have also JpaRepository class:

@Repository
public interface StudentRepository extends JpaRepository<Student, id> {
}

In my unit test code, I have a function to create a Student object:

public Student createStudentEntity() {
  // create exam, assign ZonedDateTime.now() to its "startTime" property 
  Exam exam = new Exam(ZonedDateTime.now(), "math01");

  Student student = new Student();
  student.setExam(exam);
  ...
  return student;
}

Then, I call this function in my test to save the student to database:

@ActiveProfiles("test")
@SpringBootTest
class StudentTest {

    @Autowired
    StudentRepository studentRepo;
    
    @Test
    void test_save_student()  {
        Student student = MyTestHelper.createStudentEntity();
        studentRepo.saveStudent(student);

        // The assertion fails due to the "startTime" of "exam" json object mismatch
        Assertions.assertEquals(student, studentRepo.getStudentById(student.getId()));
    }

The above test fails with message:

AssertionFailedError: expected: <Student(id=123,exam=Exam(startTime=2023-04-24T20:58:57.624489+03:00[Europe/Paris],...> but was: <Student(id=123,exam=Exam(startTime=2023-04-24T20:58:57.624489+03:00[UTC],...>

So, the error in short tells the mismatch in timezone:

• exam json value in created student is exam=Exam(startTime=2023-04-24T20:58:57.624489+03:00[Europe/Paris],
• but when fetched from database, the fetched student contains exam json with value exam=Exam(startTime=2023-04-24T20:58:57.624489+03:00[UTC].

Why the fetched student from database has exam field contains startTime that is in [UTC] while before persisting it to database the same field in exam json is treated as [Europe/Paris]?

==== UPDATE ===

I tried @Mar-Z's answer, updated my Exam class (which is not a JPA entity) to:

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Exam implements Serializable {
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss.SSSZ", timezone = "Europe/Paris")
    private ZonedDateTime startTime;
    private String code;
}

But it doesn't help, I still have the same issue: The fetched student from database having json field exam which contains the startTime part having [UTC] timezone.

Answer 1

You are using the type ZonedDateTime in your Java code. It contains both time and the time zone.

With the call of ZonedDateTime.now() you will get default time zone of your application. Which is apparently Europe/Paris.

As you don't define any formatter for JSON serialization it will be serialized to number of milliseconds since January 1st, 1970. And it will be stored in the database as a number. In that moment the information about the time zone is lost. After reading back from the database this number will be deserialized to ZoneDateTime but using UTC.

That's why both times compares as not equal.

Solution

Use @JsonFormat annotation to provide time zone id in the string representation. (Please mind double V, not a W)

@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss.SSSZ VV")
private ZonedDateTime startTime;

In the application.properties override Jackson defaults for adjusting the time zone to UTC.

spring.jackson.serialization.write-dates-with-zone-id=true
spring.jackson.serialization.write-dates-with-context-time-zone=false
spring.jackson.deserialization.adjust-dates-to-context-time-zone=false