Re: timestampdiff() implementation - Mailing list pgsql-jdbc

Thanks for taking the time to consider the question.  Let me clarify my first post.  My question is not with the implementation of timestampadd() but timestampdiff().  My second example was meant to show that while using timestampadd and timestampdiff are not symmetric.  Using timestampadd 1:00 + 90min is 2:30 (as expected), while using timstampdiff 2:30 - 1:00 is 30min. 

Both SqlServer and MySQL give a different answer: timestampdiff(MINUTES, 1:30, 2:30) = 90

Matt

From: "Thomas Kellerer" <spam_eater@gmx.net>
Date: Dec 14, 2015 11:24 PM
Subject: Re: [JDBC] timestampdiff() implementation
To: <pgsql-jdbc@postgresql.org>
Cc:

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

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc