Re: timestampdiff() implementation

Matthew Bellew schrieb am 15.12.2015 um 03:05:
> 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
>
> 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

This looks correct to me. The above expressions returns the result of "90 minutes after 01:00:00" which *has* to be 02:30:00
What result would you expect of adding 90 minutes to 01:00:00?

Plus: it is the same result as "plain" SQL would return.

The following statement:

SELECT {fn timestampadd(SQL_TSI_MINUTE,90,{ts '2000-01-01 1:00:00'})} as jdbc_result,
timestamp '2000-01-01 01:00:00' + interval '90' minute as sql_result;

using a JDBC based SQL client returns:

jdbc_result | sql_result
------------------------+------------------------
2000-01-01 02:30:00.001 | 2000-01-01 02:30:00.001

Both the jTDS and the Microsoft driver for SQL Server also return 2000-01-01 02:30:00.001 for {fn timestampadd(SQL_TSI_MINUTE,90,{ts '2000-01-01 01:00:00'})}

So I don't see what timestampadd() does wrong.

> I believe the correct translate {fn timestampdiff{SQL_TSI_MINUTE,...)} is something like
>
> EXTRACT(EPOCH FROM ...)/60;
>

No, the equivalent of {fn timestampdiff{SQL_TSI_MINUTE,...)} is adding an interval (minutes) to a timestamp as shown above.

Thomas