Re: fomatting an interval

I was considering doing something with substring, excpet I couldn't count on the interval
being anything in particular.  Most of the time it is HH:MM:SS.mmm but sometimes it has
days before, and I can't count on there being .mmm at the end.  Somtimes it is just .mm or .m.

nolan@celery.tssi.com wrote:
>>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';


--
Joseph Shraibman
joseph@xtenit.com
Increase signal to noise ratio.  http://xis.xtenit.com