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
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 |