LocalDateTime and date in DB is not rounded the same

Question

I want to sign jwt token with LocalDateTime which is received after creating the user. After that, I need to check if jwt is valid and I'm achieving that with date retrieved from DB.

Problem is that LocalDateTime on creation contains nanoseconds, and date from DB does not, and then the validation fails.

I try to resolve this with formating LocalDateTime to display date without nanoseconds, but there is another problem. LocalDateTime is rounded to floor (e.g 1.222 is 1, and 1.777 is 1) and date inserted in DB is rounded like this 1.222 is 1, 1.777 is 2, so validation again fails.

I manage to resolve this by adding @Column(columnDefinition = "TIMESTAMP (6)") on my field in entity and now nanoseconds are also stored in DB, and everything works, but I'm not sure that this is the right solution.

So is there any better solution? Can I somehow round LocalDateTime same as in DB(and leave db field type as DATETIME and not TIMESTAMP) or can DB date be rounded as LocalDateTime.

I'm using spring boot 2.2.4

Can you show us how you have tried to *round* the `LocalDateTime` and maybe even how you have parsed it or a how a use creates it? — deHaar, Apr 21 '20 at 18:27
I just found that I can add ```DATETIME(3)```, this will store milliseconds in DB so I can avoid DB rounding and also keep ```DATETIME``` filed type. What do you think of this approach? — while1618, Apr 21 '20 at 18:29
I found rounding of ```LocalDateTIme``` on this [link](https://stackoverflow.com/questions/25552023/round-minutes-to-ceiling-using-java-8) — while1618, Apr 21 '20 at 18:32
Could you truncate the `LocalDateTIme` *before* saving it to the database so you know the values agree? BTW, I think that using `TIMESTAMP (6)` in the database is a *very* nice solution. — Ole V.V., Apr 22 '20 at 17:37
Similar: [In java how do we round off time to nearest hour and minute?](https://stackoverflow.com/questions/8233236/in-java-how-do-we-round-off-time-to-nearest-hour-and-minute/8233614) But I frankly don’t think that trying to mimic your database’s way of rounding is the good solution. — Ole V.V., Apr 22 '20 at 17:49
@OleV.V. Yes, truncate the ```LocalDateTime``` before saving it to the database is probably the best solution, because I do not need milliseconds to be stored. Thank you. — while1618, Apr 22 '20 at 19:02

score 3 · Accepted Answer · answered Apr 24 '20 at 04:03

I believe that this question has been answered in the comments. So to make this clear to everyone, I’d like to sum up the solutions discussed.

You said

truncate the LocalDateTime before saving it to the database is probably the best solution, because I do not need milliseconds to be stored.

It can be done like this:
```
    yourLocalTime = yourLocalTime.truncatedTo(ChronoUnit.SECONDS);
```
This makes sure that the values fall on whole seconds. No further truncation or rounding happens. Therefore the values agree from this point.
In my opinion using TIMESTAMP(6) in the database as you mentioned in the question is a very nice solution.

What I don’t think you will want to do is try to mimic the way your database rounds the value. It would require extensive testing to verify that you are doing it in exactly the same way in all corner cases. We probably don’t know whether the database uses “round even”, “round half up” or some other rounding. And we may not even be sure whether it changes in the next version of the database engine.

Link: Wikipedia article: Rounding

LocalDateTime and date in DB is not rounded the same

1 Answers1