| From: | Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com> |
|---|---|
| To: | Dave Cramer <pg(at)fastcrypt(dot)com>, Matthew Bellew <matthewb(at)labkey(dot)com> |
| Cc: | List <pgsql-jdbc(at)postgresql(dot)org> |
| Subject: | Re: timestampdiff() implementation |
| Date: | 2015-12-15 16:00:08 |
| Message-ID: | CAB=Je-FnBRGosOjV9u=HXhZ_wfUMFEuNQESNW-WjMcO5XTEnYA@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-jdbc |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Pavel Kajaba | 2015-12-17 12:37:31 | Re: Migration to Maven |
| Previous Message | Dave Cramer | 2015-12-15 15:53:25 | Re: timestampdiff() implementation |