Bug #5376
Reintroduce UDFs for handling special date-related operators
0%
History
#2 Updated by Ovidiu Maxiniuc almost 3 years ago
The investigation from #5325 lead to conclusion that in the current revision FWD lacks support for some +
/ -
operators when the operands are some kind of date
. As part of that task, I learned that H2 and PGSQL natively support only one operation: date
+/- integer
but not date
+/- <other-numeric-type>
. Injecting a cast
to integer
type on the second operand allowed the operations to be correctly performed and luckily fixed the cause of issue in that task.
- the
cast
injection solution was not tested on MSSQL dialect. A quick investigation revealedDATEADD
function for date arithmetic. Since this exists it is possible that the+
operator might not work natively with MSSQL; - only the
date + numeric-type
is handled. There is a second operationdatetime(-tz) + numeric-type
. In this case, in 4GL, the unit for second operand is the millisecond. This case was not covered by #5325. The investigations revealed that:- H2 supports such operation but the unit of the second operand is 1 day. I have no confirmation, but I expect that passing a fractional part (if present) represents the time of the day. For example, adding 2.5 means 2 days and 12 hours. If this is true, a conversion to a day unit should be straightforward;
- PGSQL seems to support a more complex date and time operations. It uses a special datatype
interval
which can be measured in different UMs. This datatype allows other operations like*
and/
to be used also; - MSSQL: as note above, this dialect features a very specific function to date-related operations and seems, by far, the most exotic one.
As conclusion: except for the basic operations already implemented as part of #5325, all dialects support date/time operations but in different ways. This a good part: in order to avoid the UDF written in Java which may affect the performance of the query because of their opacity to the query planner we can write dialect specific, low-level implementation for these operators.
If this is too complicated (to write and maintain) we hill have to finally rely on old-good Java UDF implementations.