LocalDate Format in SQLite Database Result error

I'm using SQLite for my database, but the result for date_logged is showing me an unexpected value, like 1731279600000, instead of the date I entered (e.g., 2024-11-12). I used H2 for my database previously, and it worked fine there, but it's not working correctly in SQLite. How can I fix this?

@Column(nullable = false)
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd")
private LocalDate dateLogged;

This is the code in my Log entity

@PostMapping("/logs")
public Log createLog(@RequestBody Map<String, Object> logData) {
    return logService.createLogEntry(
            (String) logData.get("username"),
            (String) logData.get("project_name"),
            Double.valueOf(logData.get("hours_logged").toString()),
            LocalDate.parse((String) logData.get("date_logged"))
    );
}

LogController

public Log createLogEntry(String username, String projectName, Double hoursLogged, LocalDate dateLogged) {
    User user = userRepository.findByUsername(username);
    if (user == null) throw new RuntimeException("User not found");
    Log log = new Log();
    log.setUser(user);
    log.setProjectName(projectName);
    log.setHoursLogged(hoursLogged);
    log.setDateLogged(dateLogged);
    return logRepository.save(log);
}

LogService

Solution

1731279600000 is the unix timestamp. Take a look: https://www.unixtimestamp.com. So it is one of the ways to store the date you provided which is 2024-11-10.

Talking a bit about sqlite, unlike other databases, it does not have date types. Instead it can be one of:

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

full docs: https://www.sqlite.org/datatype3.html

In your scenario, you can create custom setters.

So when you set it(dateLogged): it saves the value in database as unix timestamp and sets the one you want So when spring sets it(dateLoggedUnix): it sets the value and already sets the one that you want in locadate.

Example:

@Entity
class Log {
    private String projectName;
    private Double hoursLogged;
    @Transient // jpa one to make database ignore this field
    private LocalDate dateLogged;
    private long dateLoggedUnix;

    public void setDateLoggedUnix(int dateLoggedUnix) {
        this.dateLoggedUnix = dateLoggedUnix;
        this.dateLogged = Instant.ofEpochSecond(dateLoggedUnix)
                .atZone(ZoneId.systemDefault())
                .toLocalDate();
    }

    public void setDateLogged(LocalDate dateLogged) {
        this.dateLogged = dateLogged;
        this.dateLoggedUnix = dateLogged
                .atStartOfDay(ZoneId.systemDefault())
                .toEpochSecond();
    }
    // other noraml gettters and setter
}

Remember, @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd") is only for jackson library, not JPA.

Also as an alternative, you might find this answer interesting https://stackoverflow.com/a/77771796/10880357