Re: difficulty formating interval datatypes in 7.4

On Wed, Oct 19, 2005 at 16:22:29 -0400,
Chris Matheson <chris(at)centralfrontenac(dot)com> wrote:
> Hello list,
>
> I am working to format an interval in using the to_char() SQL function
> on postgresql 7.4.8. I've had nothing but disapointment so far.
> My confusion occurs when I'm trying to format using days where the days
> output would be more than 99. For example:
> I would like to do something like
> SELECT to_char('01-JAN-2001'::timestamp - '01-JAN-2000'::timestamp,
> 'ddd "days" hh24 "hours"');
> To get this
> '365 days 00 hours'
> Currently I get this
> '335 days 00 hours'
> I know that 'ddd' doesn't exist in the sense I'm thinking, but, it seems
> that either I've missed something or that to_char function wasn't
> designed with intervals in mind. The 7.4 manual lists the following
> "Warning: |to_char|(interval, text) is deprecated and should not be used
> in newly-written code. It will be removed in the next version."
> But doesn't say what the alternative is. Thanks for your help.

to_char doesn't have a lot of support for intervals.
You could extract the epoch for the interval and then divide by the number
of seconds in a day and then trunc it.