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(at)celery(dot)tssi(dot)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(at)xtenit(dot)com
Increase signal to noise ratio. http://xis.xtenit.com