Postgres Pro
Downloads
Home   >   mailing lists

Thread: timestampdiff() implementation

timestampdiff() implementation

From
Matthew Bellew
Date:
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

Re: timestampdiff() implementation

From
Thomas Kellerer
Date:
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

Fwd: Re: timestampdiff() implementation

From
Matthew Bellew
Date:
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

Re: timestampdiff() implementation

From
Dave Cramer
Date:



On 15 December 2015 at 10:49, Matthew Bellew <matthewb@labkey.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@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


Re: timestampdiff() implementation

From
Vladimir Sitnikov
Date:
As far as I can see, {fn timestampdiff(SQL_TSI_MINUTE, a, b)} is
translated as extract(minute from (b-a)), so it is not right.

So something like extract(epoch from (b-a))/60 should be used.
Here's a test case:
select x.v
     , extract(minute from x.v) extract_minute  -- returns 30
     , extract(hour from x.v) extract_hour -- returns 1
     , extract(epoch from x.v) extract_epoch -- returns 5400
     , (extract(epoch from x.v)/60)::bigint extract_epoch_div_60 -- returns 90
  from (select interval '1:30' day to second as v) as x

Vladimir