timestampdiff() implementation

From: Matthew Bellew <matthewb(at)labkey(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: timestampdiff() implementation
Date: 2015-12-15 02:05:31
Message-ID: CAJnjrPNSNE=YQeawBgrqay=J_8g9SmKmbB4Gt2W0Mx06bekN8A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

https://jdbc.postgresql.org/documentation/82/escaped-functions.html

However, this certainly seems inconsistent with other databases
interpretation of this function (certainly JDBC's definition is very
vague).

http://www.w3resource.com/mysql/date-and-time-functions/mysql-timestampdiff-function.php
https://msdn.microsoft.com/en-us/library/ms189794.aspx

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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Thomas Kellerer 2015-12-15 07:23:26 Re: timestampdiff() implementation
Previous Message Vladimir Sitnikov 2015-12-14 14:36:21 Re: [GENERAL] plpgsql function with RETURNS SETOF refcursor in JAVA