Re: formatting intervals with to_char

I haven't heard any replies from this, so in the meantime I've found a
hacky way to get the output I desire.  I'm basically calculating the
hours on the fly and piecing together a formatted string with
concatenations like this:

SELECT
(((EXTRACT(day from time_idle)*24)+EXTRACT(hour from time_idle)) || ':' ||
    EXTRACT(minute from time_idle) || ':' || EXTRACT(second from
time_idle))::interval AS myinterval
FROM
( SELECT ('2006-09-15T23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp) AS time_idle) FROM_TABLE;

If anyone knows a better/proper way to get this result, let me know.

That would be my approach though you could also replace:

EXTRACT(minute from time_idle) || ':' || EXTRACT(second from time_idle)

with:

to_char(time_idle, ':MM:SS')

If you really want to clean up your SQL you could create a function that takes any timestamp and then make the function IMMUTABLE to tell postgresql it doesn't need to rederive the results every time.