I just wanted to verify if my expectations are right regarding this. I want to use HSQLDB 2.7.2 in unit tests, but it seems expressions like this:
to_timestamp('24-04-2019 08:46:43.082893000', 'dd-mm-yyyy hh24:mi:ss.FF')
get parsed to timestamps like this (zero in first decimal place omitted)
2019-04-24 08:46:43.82893
resulting, for example, in rows getting lost between paginated queries (last page ends with T+0.082893s, but if I plug this timestamp value back to next query, the next page will start from T+0.82893s)
The documentation (https://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#N127C5) says this about the formatting:
FF - Fractional seconds. Use without repetition., suggesting that HSQL should not expect ms/μs/ns, or any fixed time unit here, but an arbitrary precision decimal.
HSQLDB translates the format string into a java.text.SimpleDateFormat. This ignores any leading zeros when parsing the fraction, and treats the fraction value as milliseconds. The next release (2.7.3) will use a java.time format which doesn't have the same problem.