> How can I format an interval? I want something like the default format but without the
> milliseconds. However if I try to format it myself I lose the parts that are greater than
> hours. http://developer.postgresql.org/docs/postgres/functions-formatting.html is not much
> help. Does anyone know can I get the default format?
Your best option is probably to use the substring function to parse out
only the parts you want, and then combine them back together again if that's
what you need. If you do that in a function, you can re-use it whenever
you need it again.
Though it isn't specifically what you're after, below is an example that
might get you started, I wrote this earlier today to give me the
functionality of the 'months_between' function in Oracle.
It isn't quite an identical replacement yet, as Oracle's months_between()
function considers the dates '2001-01-31' and '2001-02-28' to be 1 month
apart while pgsql's age() function considers them to be 28 days apart.
I may have to add a few days to the 'age' to handle this.
--
Mike Nolan
create or replace function months_between(date, date)
returns integer as
'
DECLARE
date1 alias for $1;
date2 alias for $2;
wk_years int;
wk_months int;
BEGIN
if date1 is null or date2 is null then
return NULL;
end if;
wk_years := cast( coalesce(substring(age(date1, date2)
from ''([0123456789]*) year''),''0'') as int);
wk_months := cast( coalesce(substring(age(date1, date2)
from ''([0123456789]* ) mon''),''0'') as int);
return wk_years*12 + wk_months;
END
' language 'plpgsql';