Handling timestamp in spring boot and mysql using LocalDatTime

[Puspender Tanwar]

I am using @CreationTimestamp for the createdAt column into my table. Now I want to compare the dates using LocalDateTime. The @CreationTimestamp stores the date in format like : "2020-11-20 17:36:46", but the LocalDatTime.now() gives "2020-11-20T17:36:46"

@Column(updatable = false, nullable = false)
@CreationTimestamp
@JsonSerialize(using = LocalDateTimeSerializer.class)
@JsonDeserialize(using = LocalDateTimeDeserializer.class)
private LocalDateTime createdAt;

As of now, I am replacing the "T" in "2020-11-20T17:36:46" with space and then comparing it using JPQL query.
Is there any other way to solve this? Replacing "T" with space seems a hack to me.

[Tim Holloway]

The preferred date/time value format for both databases (MySQL) and Java is UTC time. It's stored in a binary format, not in the form that your code prints it out in. You can compare two UTC timestamps just as easily as you can local timestamps, and in a world-wide format you don't get confusion about which zone (or summer time) that the timestamps are actually in.

The way that your JSON serialization parses and formats timestamps is independent of Spring or Spring Boot, regardless of whether your reference point is local time or UTC time, but there are standard date/time string formats defined for Internet use and failure to conform may cause errors or data loss. JSON's default settings allow for that.

[Rob Spoor]

Puspender Tanwar wrote:The @CreationTimestamp stores the date in format like : "2020-11-20 17:36:46", but the LocalDatTime.now() gives "2020-11-20T17:36:46"

That's not correct, you're mixing the values for storing and displaying. A database may store its dates, times and timestamps (including LocalDateTime) in one format, and display it differently. The same goes for LocalDateTime.now(). In the end, for JPA and JPQL, they're both still LocalDateTime, and for the database whatever type the column has.

For JSON it's the same. In JSON it's actually nothing but a string, but as soon as you change it back into an object it's a LocalDateTime again.

So in short: just compare the dates using >, <=, etc, and let JPA and the database take care of any conversion:

@Query("SELECT e FROM MyEntity e WHERE createdAt > :createdAt")
Stream<MyEntity> findAllCreatedAfter(@Param("createdAt") LocalDateTime cutOffTime);

Disclaimer: thiscode has not been tested, or even compiled. It's the idea that counts.