Serialisation format of ZoneDateTime stored as JSONB in PostgreSQL

Question

trying to store complex data in PostgreSQL as JSONB.
Storage works fine except the format of attributes of type java.time.ZonedDateTime.

1 - Given is the entity Route:

@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
…
class
…

@Column(name = "stops", columnDefinition = "jsonb")
@Type(type = "jsonb")
@Valid
private List<Stop> stops;
…

which has an @Embeddable object Stop that holds attribute:

@Column(name = "date_from")
private ZonedDateTime dateFrom;

2 - When the POST REST-endpoint receives:

{…
"dateFrom": "2018-07-28T17:47:50.331+02:00",
…}

Mapping of entire JSON to entity works!

BUT

3 - Then the postgreSQL database stores as date:

{…
"loadingDateFrom": {
    "hour": 17,
    "nano": 331000000,
    "year": 2018,
    "zone": {
        "id": "+02:00",
        "rules": {
            "fixedOffset": true,
            "transitions": [],
            "transitionRules": []
        },
        "totalSeconds": 7200
    },
    "month": "JULY",
    "minute": 47,
    "offset": {
        "id": "+02:00",
        "rules": {
            "fixedOffset": true,
            "transitions": [],
            "transitionRules": []
        },
        "totalSeconds": 7200
    },
    "second": 50,
    "dayOfWeek": "SATURDAY",
    "dayOfYear": 209,
    "chronology": {
        "id": "ISO",
        "calendarType": "iso8601"
    },
    "dayOfMonth": 28,
    "monthValue": 7
},  
…}

Expected would be the same as the endpoint receives (date string):
"2018-07-28T17:47:50.331+02:00"

(other example: if stored as postgreSQL timestamp, like a different attribute in entity Stop, serialisation as date string works fine)

Calling the GET REST-endpoint delivers the complex date-structure, which is bad since date handling in frontend is complicated.

Question:

How can it be achieved that postgreSQL stores the date as date string, not a s complex object?

After a lot of debugging i think it has something to do with the Jackson serialiser. I see the DefaultSerializer is getting a ZonedDateTime object. After serialisation the complex data structure is stored.

Is there a way of configuration to format the date instead writing own serialiser/deserialiser?
I tried:
- different @JsonFormat(…) at date attribute but got always "JSON parse error" for deserialisation. Anyway I don't want do specify a format. This should be out-of-the-box.

Config:
jhipster/spring-boot project
- compile "com.fasterxml.jackson.datatype:jackson-datatype-jsr310"
- compile "com.fasterxml.jackson.core:jackson-databind"
- spring: jackson: serialization.write_dates_as_timestamps: false

Maybe another question is, who is responsible for that complex format? Jackson, Hibernate, Postgresql, ZonedDateTime itself? — lxndr, Jul 31 '18 at 12:51

GolamMazid Sajib · Accepted Answer · 2018-07-31T14:42:04.017

Use CustomSerializer:

public class ZonedDateTimeSerialzier extends JsonSerializer<ZonedDateTime>{
@Override
public void serialize(ZonedDateTime value, JsonGenerator gen, SerializerProvider serializers) throws IOException {
    String parseDate = null;// parse here zoned date time
    gen.writeString(parseDate);
}
}

then add this:

@Column(name = "date_from")
@JsonSerialize(using = ZonedDateTimeSerialzier.class)
private ZonedDateTime dateFrom;

If you want to do it globaly then write this bean in configuration class:

@Bean
public Jackson2ObjectMapperBuilder configureObjectMapper() {
    Jackson2ObjectMapperBuilder builder = new Jackson2ObjectMapperBuilder();
    SimpleModule zonedDateTimeSerializer = new SimpleModule();
    zonedDateTimeSerializer.addSerializer(ZonedDateTime.class,new ZonedDateTimeSerialzier());

    builder.modules(zonedDateTimeSerializer);
    return builder;
}

Or if you want timestamp add it in application.properties:

spring.jackson.serialization.write-dates-as-timestamps=true

or application.yml:

spring:
  jackson:
    serialization: 
      write-dates-as-timestamps: true

Thanks @sajib. Yes a custom serialiser is a correct way. But as i asked, is there any configuration to handle the format of the date than a custom serialiser? Who is defining the (default) format in this complex form… Jackson, Hibernate, Postgresql, ZonedDateTime itself? — lxndr, Jul 31 '18 at 12:50
you can set it globally instead of using field variable. answer updated — GolamMazid Sajib, Jul 31 '18 at 14:42

score 0 · Answer 2 · answered Jul 04 '19 at 07:57

INFO:
I marked @sajib as correct answer, since no other solution has come up.
I used:

@JsonSerialize(using = ZonedDateTimeSerializier.class)
@JsonDeserialize(using = ZonedDateTimeDeserializier.class)

to serialize Date to String and deserialize String to Date

Serialisation format of ZoneDateTime stored as JSONB in PostgreSQL

2 Answers2