java.time.format.DateTimeParseException: Text '2019-02-16 09:29:32.959' could not be parsed, unparsed text found at index 10

Question

I have a column in MySQL with type timestamp contains "2019-2-16 8:00:00" and I'm using spring boot to get local today and compare with it. I did many search on google but there is no help. Here is my final code:

   @Override
public List<TaskDTO> getTodayTask(int employeeID) {
    Date today = getToday();
    List<Task> listOfTask = taskRepository.findByAssigneeIdAndAndStartTime(employeeID, today);
    List<TaskDTO> listOfDTO = new ArrayList<>();
    for (Task t : listOfTask) {
        listOfDTO.add(taskMapper.toDTO(t));
    }
    return listOfDTO;
}


private Date getToday() {
    Calendar calendar = Calendar.getInstance();

    Timestamp timestamp = new Timestamp(calendar.getTime().getTime());

    DateTimeFormatter format = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    OffsetDateTime odt = OffsetDateTime.parse ( timestamp.toString() , format );

    Instant instant = odt.toInstant();

    return Date.from(instant);
}

and my repository:

    @Query("SELECT i FROM Task i WHERE i.assigneeId = ?1 and CAST( i.startTime as date) = ?2")
List<Task> findByAssigneeIdAndAndStartTime(int assigneeID, Date startTime);

I found out that java.util.Date is deprecated so I'm trying to find another way.

My entity:

@Entity

@Table(name = "task") public class Task implements Serializable {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long id;

@Column(name = "title")
private String title;

@Column(name = "assigner_employee_id")
private int assignerId;

@Column(name = "assignee_employee_id")
private int assigneeId;

@Column(name = "workplace_id")
private int workplaceId;

@Column(name = "schedule_id")
private Long scheduleId;

@Column(name = "start_time")
private Timestamp startTime;

@Column(name = "end_time")
private Timestamp endTime;

@Column(name = "check_in_time")
private Timestamp checkInTime;

@Column(name = "attendance_status")
private Integer attendanceStatus;

@Column(name = "status")
private Integer status;

@Column(name = "date_create")
private Timestamp dateCreate;

@Column(name = "priority")
private Integer priority;

@Column(name = "description")
private String description;

public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public String getTitle() {
    return title;
}

public void setTitle(String title) {
    this.title = title;
}

public void setAssignerId(int assignerId) {
    this.assignerId = assignerId;
}

public void setAssigneeId(int assigneeId) {
    this.assigneeId = assigneeId;
}

public int getAssignerId() {
    return assignerId;
}

public int getAssigneeId() {
    return assigneeId;
}

public int getWorkplaceId() {
    return workplaceId;
}

public void setWorkplaceId(int workplaceId) {
    this.workplaceId = workplaceId;
}

public Long getScheduleId() {
    return scheduleId;
}

public void setScheduleId(Long scheduleId) {
    this.scheduleId = scheduleId;
}

public Integer getAttendanceStatus() {
    return attendanceStatus;
}

public void setAttendanceStatus(Integer attendanceStatus) {
    this.attendanceStatus = attendanceStatus;
}

public Integer getStatus() {
    return status;
}

public void setStatus(Integer status) {
    this.status = status;
}

public Integer getPriority() {
    return priority;
}

public void setPriority(Integer priority) {
    this.priority = priority;
}

public String getDescription() {
    return description;
}

public void setDescription(String description) {
    this.description = description;
}

public Timestamp getStartTime() {
    return startTime;
}

public void setStartTime(Timestamp startTime) {
    this.startTime = startTime;
}

public Timestamp getEndTime() {
    return endTime;
}

public void setEndTime(Timestamp endTime) {
    this.endTime = endTime;
}

public Timestamp getCheckInTime() {
    return checkInTime;
}

public void setCheckInTime(Timestamp checkInTime) {
    this.checkInTime = checkInTime;
}

public Timestamp getDateCreate() {
    return dateCreate;
}

public void setDateCreate(Timestamp dateCreate) {
    this.dateCreate = dateCreate;
}

}

I got an error:

java.time.format.DateTimeParseException: Text '2019-02-16 09:29:32.959' could not be parsed, unparsed text found at index 10

I did many search to find out but still got no suitable answer. Thank you.

Answer 1

You are correct and more than that. While not officially deprecated yet, the classes Date, Calendar and Timestamp are long outdated and because of their design issues we should not use them when we can avoid it.

I am no Spring Boot user and don’t know what to pass to the query instead of an old-fashioned Date. I would much expect that a more modern solution exists using one of the modern types from the java.time API (where OffsetDateTime, Instant and DateTimeFormatter belong). If you only need to pass a date, not the time of day, then a LocalDate would be logical, but as I said, I do not know.

If you indispensably need a Date for the Spring Boot query:

private static Date getToday() {
    Instant instant = Instant.now();
    return Date.from(instant);
}

What went wrong in your code? To parse into an OffsetDateTime both of your date-time string and your format pattern string need to specify not only date but also time of day and UTC offset (or you need to supply them some other way). In your code, OffsetDateTime successfully parsed 2019-02-16 as yyyy-MM-dd, and since the format pattern wasn’t longer, it complained that the string from Timestamp.toString was. However, shortening the string would not have helped because then the time of day would have been missing, and in any case the UTC offset would, so it couldn’t have worked.

Answer 2

Problem is with the pattern you are using. Try this:-

DateTimeFormatter format = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");

Instead of:-

DateTimeFormatter format = DateTimeFormatter.ofPattern("yyyy-MM-dd");