Win a copy of High Performance Python for Data Analytics this week in the Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Bear Bibeault
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Jj Roberts
  • Carey Brown
Bartenders:
  • salvin francis
  • Frits Walraven
  • Piet Souris

Handling timestamp in spring boot and mysql using LocalDatTime

 
Ranch Hand
Posts: 650
2
Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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"




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.
 
Saloon Keeper
Posts: 23055
157
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
Sheriff
Posts: 22057
113
Eclipse IDE Spring VI Editor Chrome Java Ubuntu Windows
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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:

Disclaimer: thiscode has not been tested, or even compiled. It's the idea that counts.
 
I'm full of tinier men! And a tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic