I recently discovered surprising behavior of the {fn timstampdiff()} function. I'm seeing that this function seems to extract a portion of the interval rather than converting it to the requested units. The documentation makes it obvious that this is the intended implementation
However, this certainly seems inconsistent with other databases interpretation of this function (certainly JDBC's definition is very vague).
Without resorting to what other databases seem to think is correct, I think there are two arguments that this is the wrong interpretation.
1) It does not interoperate with timestampadd in an arithmetic way
SELECT {fn timestampadd(SQL_TSI_MINUTE,
90,
{ts '2000-01-01 1:00:00'}
)} as expr1
RETURNS 2000-01-01 02:30
SELECT {fn timestampadd(SQL_TSI_MINUTE,
{fn timestampdiff(SQL_TSI_MINUTE, {ts '2000-01-01 1:00:00'}, {ts '2000-01-01 2:30:00'})},
{ts '2000-01-01 1:00:00'}
)} as expr1
RETURNS 2000-01-01 01:30
2) It is difficult to find convert an interval to desired units (using JDBC functions) without to postgres specific syntax.
I believe the correct translate {fn timestampdiff{SQL_TSI_MINUTE,...)} is something like
EXTRACT(EPOCH FROM ...)/60;
Interested in comment, and on my way to writing a pre-processor so this function acts consistently....
Matt
