How to send/parse '-infinity' and 'infinity' through LocalDateTime

[AntoineDuComptoirDesPharmacies]

Hi,

We are currently trying to use a DB date column where default value is '-infinity'.
Our Bean map this column with the java LocalDateTime.
We would like to send this default value when our Bean's attribute is NULL / UNSET.

Is there any magical way to tell Ebean to use this particular value as default one on insert / update ?

Then, we would like to be able to read this custom value in Ebean. What will be the result in Java ?
A NULL LocalDateTime ?

Thanks in advance for your help/tips.
Yours faithfully,
LCDP

[jnehlmeier]

Ebean has ScalarTypeConverter to provide custom scalar types, e.g. mapping a custom date class to a type that Ebean/JDBC understands. JPA has AttributeConverter which does the same. But in both cases you have to decide which type you want to convert to. Given that you cannot express -infinity as Java Date you would need to map to String or Double. Then it depends if the JDBC driver of your DB is fine receiving a String or Double for a date column.

When reading -infinity your only option is to either use NULL in Java or map it to something like 0001-01-01 00:00:00.000. In either case it would be far more simpler to refactor your DB to use either NULL or 0001-01-01 00:00:00.000 as default value instead of -infinity. This avoids the whole problem of -infinity not being a date.

[AntoineDuComptoirDesPharmacies]

Hi @jnehlmeier,

Thank you for these details. So we may use ScalarTypeConverter /AttributeConverter to convert a LocalDateTime (or class inheritance of this one) to a Scalar String, with hope that DB can take a String when filling a PostgreSQL date type.
We are going to dig into this idea.

At the moment we are using NULL as default but it involves the following query when comparing date :
SELECT * FROM the_table WHERE (valid_from <= now() or valid_from is null)

The query planner have hard time to use index for this query because of the OR.
Sometimes he use a combination of two BITMAP but it is not very efficient.

Our goal was to change the NULL to '-infinity' in order to simplify the query and help query planner :
SELECT * FROM the_table WHERE valid_from <= now()

An other idea was to use coalesce with an index on it but it was not very efficient in our experiments. Example :
SELECT * FROM the_table WHERE coalesce(valid_from, '-infinity') <= now()

[jnehlmeier]

It sounds like the_table is very, very large? I do quite some date comparison queries similar to yours and they usually run in acceptable time. There are no other WHERE conditions in your query to further reduce the amount of rows? Only the date range?

Sometimes, if the situation allows it, a BRIN index can greatly reduce the query time. But a BRIN index only works if rows inserted in the table implicitly produce a sorted column in the table and on disk. So usually these tables are append-only and dates cannot be freely chosen. See https://www.crunchydata.com/blog/postgres-indexing-when-does-brin-win