Normalize timestamp values or have multiple SQL queries

Question

I have a Spring Boot application to implement a RESTful API. One of the GET requests have two query parameters: from and until (of type date). Those parameters are optional, so user-agents can send both, none or just one of them.

The query parameters are then used ("down") in the flow to filter data sets by (a typical) created_on column in the database using a BETWEEN clause. The problem for me is that I'm using Spring Data JPA, so any nullable value will fail the query with some exception.

So I was thinking to:

1. Normalize the values when received (from, if null will be set to Instant.MIN; until, if null will be set to Instant.now), then I can keep the query without modification
2. Leave the values as they come, and have 4 queries: when both are null, when from is not null, when until is not null, when none are null

Not sure if there is a better alternative. I can't do a custom SQL query for the moment — that's a constraint on my end.

Eventually the first alternative is easier, but I'm not sure if going with those defaults is a good idea. I would like better to execute the SQL query accordingly, but having a wide range (in case non of the parameters are sent) should have certain impact on the performance of the query.

Any advice?

Answer 1

Gah! Spare me from all these nulls!

The problem with null is it doesn’t tell you exactly what it means. Leave this field blank when displayed? Don’t set any upper limit? Report that no matching date was found?

It’s far better to bake those meanings explicitly into your model than to just clutter it with a pile of mysterious nulls to decode later.

This is a serious problem that can be worth throwing an entire class at. It can sometimes be handled with collections that can be left empty. Or maybe monads if you’re feeling fancy. But please realize that letting in a simple null now means loads of null checking later.

Now sure, these are dates. But they are dates with special cases. Respect that enough to deal with it properly. You can bake the logic into real classes that know what to do. The hard part will be thinking up some good names.