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