Thread: formating interval question

I have an interval field which is used to calculate total hours.

At present it outputs as days hours minutes

Is there any way to output as total hours

I have tried to_char(field,'HH:MM')

but I get wierd results like -51 or -5

mike <mike@bristolreccc.co.uk> writes:
> I have an interval field which is used to calculate total hours.
> At present it outputs as days hours minutes
> Is there any way to output as total hours

You could always EXTRACT(EPOCH ...) to get total seconds and then
divide.  I am not sure whether EXTRACT(HOUR ...) would give the
same answer, or just the hours portion of the normal printout.
But anyway, see EXTRACT.

            regards, tom lane

On Fri, 24 Sep 2004 11:58:44 +0100
mike@bristolreccc.co.uk (mike) wrote:

> I have an interval field which is used to calculate total hours.
> At present it outputs as days hours minutes
> Is there any way to output as total hours
> I have tried to_char(field,'HH:MM')
> but I get wierd results like -51 or -5
>

From the top of my head i think could probably to do something like
this:

    extract(epoch from your_time_interval)/3600


It basically finds the "age" of your interval in seconds. The /3600 is
to find the result in hours.

If you for instance want the result with just one decimal like xxxx.y
then you can do it like this:

    round(extract(epoch from your_time_interval)/360)/10

I hope this helps.


Best regards
Jesper K. Pedersen