How to format a LocalDateTime field in a POJO persisted to a BLOB

Question

I have a POJO that I'm storing in JSON format in a BLOB column on a MySQL database table. The POJO in question has a LocalDateTime field and whenever I persist data to the column the LocalDateTime field takes the format described in this question, whereas I would like it to be represented in the format yyyy-MM-dd hh:mm:ss

I have tried implementing the solutions suggested in the linked post but none of them have worked. My guess is that it's something to do with the fact that the field is part of a POJO in a BLOB as opposed to a traditional Timestamp field.

This is the code of my POJO as it stands

public class MyPOJO {

    private Long id;

    @JsonFormat(pattern="yyyy-MM-dd")
    @DateTimeFormat(iso = DateTimeFormat.ISO.TIME)
    private LocalDateTime dateTime;

    private String someText;

    //Constructors

    //Getters and setters

}

Any input would be welcome.

This is the current JSON output:

{
"id" : 1,
"dateTime" : {
    "year" : 2010,
    "month" : "JANUARY",
    "dayOfMonth" : 1,
    "dayOfWeek" : "FRIDAY",
    "dayOfYear" : 1,
    "monthValue" : 1,
    "hour" : 2,
    "minute" : 2,
    "second" : 0,
    "nano" : 0,
    "chronology" : {
      "id" : "ISO",
      "calendarType" : "iso8601"
    }
  }, 
  "someText": "someText"
}

Just use `@JsonFormat(pattern="yyyy-MM-dd")` on field nothing else, it should work. make sure you are sending exact MyPOJO object as response — Eklavya, Aug 09 '20 at 16:13

score 0 · Answer 1 · edited Aug 10 '20 at 07:40

0

I think you should use @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss") because you also need those time value. if no replace with LocalDate and leave your current format of regex.

edited Aug 10 '20 at 07:40

Melchizedek

1,057
17
29

answered Aug 09 '20 at 16:16

Misa D.

183
10

score 0 · Answer 2 · answered Aug 10 '20 at 07:54

The correct annotation for your case should be:

@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd hh:mm:ss")
    private LocalDateTime dateTime;

The full question describing this solution could be found here: Spring Data JPA - ZonedDateTime format for json serialization. But also, why do you persist your POJO into BLOB? at the very least, use CLOB (BLOB stands for Binary Large OBject, and CLOB stands for Character Large OBject). But really you probably should concider VARCHAR2 field that should be enough in most if not all the cases.

How to format a LocalDateTime field in a POJO persisted to a BLOB

2 Answers2