Date DB type is not properly mapped to LocalDate

[Nahuel92]

Hi there!

I'm using Micronaut Data JDBC + Postgres and, recently, while creating tests, I found that the DATE DB data type doesn't properly map to LocalDate from Java. Interestingly, it seems that the production behavior is the expected one (I still need to verify this).

To replicate this, you can skip over and see the provided PoC app. But in case you want to do it manually:

1. Create a Micronaut Data + TestContainers (or Micronaut Test Resources) skeleton app.
2. Add a simple entity with its id field and a LocalDate one.
3. On the DB side, create a DATE field that will be mapped to the LocalDate one.
4. Create a test that:
   1. Sets up a DB.
   2. Creates an entity, fetches it from the DB using findById(). Make sure you don't use the entity returned by the save() method. This is important.
5. Compare the LocalDate value from the original entity against the value from the saved entity. The later one will be exactly one day behind the original value.

I'm not sure if this is a bug somewhere, or anything that I'm missing, so I would like to ask for any help you can provide.

Here's my PoC app with everything ready:
https://github.com/Nahuel92/poc-projects/tree/master/micronaut/date-field-bug

Any help/hint is appreciated. If anything is missing here, please let me know.
Thanks!

[dstepanov]

People reported something similar before, but it was a problem with a different timezone of the app vs db

[Nahuel92]

Hi dstepanov!!

I checked the time zones of both DBs: the one created by TestContainers and the real one, and both are using UTC. I also noticed that this seem to happen only when I run tests similar to what I shared in the PoC app, as our QA engineer verified in different environments and the app behaves well so far.

Now that you mentioned it, it's quite possible that the app is using different time zones. I live in a different place from where the app usually runs, so that's a good hint for me to look into. What was confusing to me is that I always thought that LocalDate was unaffected by different time zones because it doesn't store time.

[Nahuel92]

I just tested with TimeZone.setDefault(TimeZone.getTimeZone("UTC")); at the beginning of my test, and it passed without any issue. It seems odd to me to have to use it, but it worked.

[Nahuel92]

Hi @dstepanov!

Hey, I forgot to mention that using JdbcOperations I haven't seen this issue happening. I updated the provided PoC with a new test that shows this. I just wanted to bring this up just in case it matters.

[radovanradic]

This has been reported some time ago #2096.
I think there is a bug coming from micronaut-core. Here LocaleDate is being converted to Date using UTC: https://github.com/micronaut-projects/micronaut-core/blob/4.7.x/context/src/main/java/io/micronaut/runtime/converters/time/TimeConverterRegistrar.java#L212 and when converting back to LocaleDate in micronaut-data https://github.com/micronaut-projects/micronaut-data/blob/4.10.x/data-runtime/src/main/java/io/micronaut/data/runtime/convert/DataConversionServiceFactory.java#L662-L665 it is using default timezone.
This is why it works when UTC is explicitly set in the application.
I believe micronaut-core is not doing correct conversion using UTC.

[Nahuel92]

Hi radovanradic!

Thanks for sharing that info. I can go ahead and open a bug in the Micronaut Core project for this data conversion issue if that's ok for you.

[radovanradic]

No, will try to resolve the issue in micronaut-data.
Thanks

[Nahuel92]

Thank you, guys!! I really appreciate your help on this

[radovanradic]

Should be resolved by PR #3248

[Nahuel92]

Hi radovanradic!
I'll take a look and will let you know.
Thank you for your help and work!!

[Nahuel92]

Sorry for the delay. I can confirm that your fix solved the issue. Thanks a lot, radovanradic!