Re: sum timestamp result in hours

On Fri, Nov 07, 2008 at 06:10:15AM -0800, paulo matadr wrote:
> select sum(age(rgat.rgat_tmencerramento, rgat.rgat_tmregistroatendimento))
> from atendimentopublico.registro_atendimento rgat

[... lots of complicated and irrelevant SQL]

> Result: 1 year 4 mons 88 days 51:42:00
>
> I need help to view result query only in hours, the timestamp columns
> in bold above.

a lot of people don't have clients that are capable of displaying bold
text hence the above query is somewhat meaningless. it appears easy to
express the above question with resorting to strange formatting which
would exclude less people from helping. Stating the question more
simply would also help; I'd interpret your request as being something
like:

I'm trying to get the sum of differences between two timestamp
columns, the query should return the number of hours in total.
I've tried this but it doesn't work:

SELECT sum(age(datecol1,datecol2) FROM tbl;

as it gives me "1 year 4 mons 88 days 51:42:00" and I can't figure out
how to turn this into a total number of hours.

If I've interpreted your request correctly; I think you don't want to
use the age() function, just a simple subtraction would do. You can
then use the extract() function to pull the resulting interval apart and
get your result.

Sam