Re: timestampdiff() implementation

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 15 December 2015 at 10:49, Matthew Bellew <matthewb(at)labkey(dot)com> wrote:

> 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
>
Shouldn't this be 60 ??

>
> Matt
>
>
> From: "Thomas Kellerer" <spam_eater(at)gmx(dot)net>
>> Date: Dec 14, 2015 11:24 PM
>> Subject: Re: [JDBC] timestampdiff() implementation
>> To: <pgsql-jdbc(at)postgresql(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>
>