formating interval question

Started by mikeover 21 years ago3 messagesgeneral
Jump to latest
#1mike
mike@bristolreccc.co.uk

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

#2Jesper K. Pedersen
jkp@solnet.homeip.net
In reply to: mike (#1)
Re: formating interval question

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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: mike (#1)
Re: formating interval question

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