Micronaut Data JDBC persisting dates as the day before when connected to a Postgresql database

[cpichei]

Task List

• [x ] Steps to reproduce provided
• [ ] Stacktrace (if present) provided
• [ x] Example that reproduces the problem uploaded to Github
• [ x] Full description of the issue provided (see below)

Steps to Reproduce

1. Create a class with @MappedEntity annotation that has a LocalDate field and persist the entity with Micronaut Data JDBC connected to a Postgresql database
2. Check the date that's persisted, and you will find that the date is one day before as the one provided to the entity

Expected Behaviour

The date that's persisted in the database should be the same that was provided.

Actual Behaviour

The date that's persisted in the database is always one day before what was provided. Oddly enough, when doing extracting queries using the correct date, Micronaut Data is able to find and retrieve the entity correctly. This problem does not happen with a LocalDateTime field.

What happens is the following, in order:

• An entity with a LocalDate field is persisted
• If you query the persisted information using a database manager, you will see that the date is persisted as one day before (I.e: if you persist a date like 2023/12/15 it will be shown in the database as 2023/12/14)
• If you query the persisted information by the date, following the previous example of retrieving the information by providing 2023/12/15 as the date, Micronaut Data finds it and maps the information correctly to the LocalDate field of the class with the correct value of 2023/12/15.

Note: this is not a timezone issue, as both the database and the OS have the correct timezone, and dates persisted directly on the database using regular INSERTs are registered correctly.

Environment Information

• Operating System: Linux Debian Bullseye
• Micronaut Version: 3.8.7
• JDK Version: 11.0.18
• Micronaut Data JDBC Version: 3.9.6
• Postgresql Database version: 13.9
• Postgresql JDBC driver version: 42.6.0

Example Application

localdateissue.zip

Edit: added Postgresql database version Second edit: added updated version of the project that also persists a localdatetime for comparison purposes.

[dstepanov]

Please provide a sample app that reproduces it.

[cpichei]

Please provide a sample app that reproduces it.

Done, I updated my initial post.

It has a test that does a comparison check with LocalDate.now() and the one that's retrieved from the database. I assumed that the date was being retrieved correctly, but I was wrong. Micronaut Data does retrieve the object but the date is incorrect (it's one day behind).

[cpichei]

Ok, so I was trying to find a solution to this and found that I have to put this line of code before any persisting operation or all LocalDates are going to be persisted with a one day difference:

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

So, for the test case I provided, in order to be successful I have to do this:

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
        issueRepository.save(new Issue(LocalDate.now()));

        Optional<Issue> persistedIssue =
                issueRepository.findByIssueDate(LocalDate.now());

        assertTrue(persistedIssue.isPresent() &&
                persistedIssue.get().getIssueDate().equals(LocalDate.now()));

Or this in Application.java

public class Application {

    public static void main(String[] args) {
        TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
        Micronaut.run(Application.class, args);
    }
}

Specifying -Duser.timezone=UTC in the jvm options when running the app does not work at all when it should have the same effect as the above line of code.

As for having to specify the timezone in UTC, I guess this is needed as Postgresql stores dates and times in UTC by default, although this does not explain why LocalDateTime works without any problems at all yet LocalDate fails when it does not even have a time component.

[radovanradic]

I looked at the attached app, but this test is not failing or I am missing something? Tried with similar app with Micronaut Launch and test resources and also with actual DB on local machine. In both cases this test is passing.

[cpichei]

I looked at the attached app, but this test is not failing or I am missing something? Tried with similar app with Micronaut Launch and test resources and also with actual DB on local machine. In both cases this test is passing.

What timezone are you in? I guess in my case the problem is that I am UTC-4, and maybe the date is being sent to the database with time 00:00:00 and when timezone is accounted for it's giving me the day before. I know this should not make any sense as a date datafield should not have any time component to it, but it's all I can think of.

Please have a look at the following screenshot. Here you can see I persisted a localdatetime as well as a localdate. The localdatetime is correctly persisted, yet the date is one day before. I will update the OP with another version of the project which persists a localdatetime along with a localdate.

[cpichei]

@radovanradic , any news about this issue?

[radovanradic]

Not really, if app and db server are in diff timezone I suppose it is difficult to work with date types. Maybe using LocalDateTime instead of LocalDate would resolve the issue, but not sure it if works for your case. Also, not sure if this is caused by Micronaut, would the same happen with just using Jdbc directly (because of conversion between app and db server)? I didn't reproduce issue on my machine probably because db server is on my machine and time zones are matching between postgres db and app (tried with various time zones on my machine and it always worked). Also, using this

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

looks like ok solution (or workaround) for this issue to me.

[lightbody]

Just sharing my experience: I had a similar issue, though I could only reproduce it when running my unit tests but not when running my app locally, in staging, or prod. Turns out the difference was when running the regular app, we fix the JVM timezone to UTC, but our test fixture didn't, so it was taking in my local timezone (UTC-7), which caused this same behavior.

So... for future impacted users: always pin your DB + JVM (in all ways it can run) to UTC. You'll be happy you did :)

[pixnbit]

Just sharing my experience: I had a similar issue, though I could only reproduce it when running my unit tests but not when running my app locally, in staging, or prod. Turns out the difference was when running the regular app, we fix the JVM timezone to UTC, but our test fixture didn't, so it was taking in my local timezone (UTC-7), which caused this same behavior.

So... for future impacted users: always pin your DB + JVM (in all ways it can run) to UTC. You'll be happy you did :)

wow, same thing here. And here is you would set the UTC timezone in gradle if anyone sees this:

test {

    jvmArgs '-Duser.timezone=UTC'

}