Hi,
Does anyone know if it's possible to calculate the number of days (or
weeks, seconds, minutes, or what-have-you) in a given interval without
casting to EPOCH and performing manipulations there?
PostgreSQL usually returns intervals created by subtraction in days
and smaller fragments, I would like to know if it's possible for it to
return years. (Integer division of the number of days by 365 would
produce an almost-correct result, but it's rather inelegant.)
Subtracting two timestamps gives a day count:
cal=> select timestamp 'today' - timestamp 'may 1, 2000';?column?
----------727 days
(1 row)
Subtracting two days-only intervals gives the expected result:
cal=> select interval '6000 days' - interval '100 days';?column?
-----------5900 days
(1 row)
But it doesn't work for all like-unit intervals: some are reported
using other quantities:
cal=> select interval '6000 minutes' - interval '100 minutes'; ?column?
--------------4 days 02:20 <--- not the expected '5900 minutes'
(1 row)
And most notably, it doesn't work for years, where it would be most useful:
cal=> select timestamp 'jan 24, 1998 00:00' - timestamp 'jan 24, 1990 00:00';?column?
-----------2922 days
(1 row)
cal=> select extract (years from timestamp 'jan 24, 1998 00:00' - timestamp 'jan 24, 1990 00:00');date_part
----------- 0
(1 row)
My aim is to make it easier to write a function that manipulates years
(the code fragment in question takes two timestamps, A and B, and an
integer N, and subtracts A from B to see if they differ by a multiple
of N years. If not, then it adds years to B to ensure that A and B
differ by a multiple of N).
Any ideas would be appreciated.
Cordially,
Joe Barillari