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.

Are you trying to persist the timezone as well as the date? What is the type of the column in the database? I don't believe JPA supports persisting the timezone as it isn't standard in databases - you are going to be stuck storing, converting and combining a timestamp/long data value with a timezone preference separately - see stackoverflow.com/q/56647962/496099 — Chris
– Chris, Commented Apr 24, 2023 at 23:03
@Chris, the type of the column for exam field is json as indicated in my post. The startTime is a property of Exam class. So, I am persisting Exam property of Student as a json. — user842225
– user842225, Commented Apr 25, 2023 at 5:34
This is not a JPA issue but a Hibernate JSON serialization issue - show the JSON formed and stored in your DB with the options you've used to generate it and it might help show what is going wrong (serialization, deserialization or both). Also see stackoverflow.com/a/39086534/496099 as you might need to configure Jackson to support ZonedDateTime (and it is version specific) — Chris
– Chris, Commented Apr 25, 2023 at 14:38

Mar-Z · Accepted Answer · 2023-04-25 11:35:01Z

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

Hi, thanks! Should I apply the @JsonFormat to the Exam field of Student or to the startTime field of Exam class?
and how to dynamically get the current timezon in this annotation instead of hardcode Europe/pairs?
I tried your solution, the same issue still exists. I hope you are aware that the whole Exam property of Student class is a json field, the issue is in the startTime of Exam and the Exam is not a JPA entity. Please see my updated PO. Thanks.
Yes, my first solution was not perfect. Please check the edited version
With your edited version, I get error No enum constant com.fasterxml.jackson.databind.SerializationFeature.adjust-dates-to-context-time-zone

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related