[GENERAL] sum timestamp result in hours
select sum(age(rgat.rgat_tmencerramento, rgat.rgat_tmregistroatendimento)) from atendimentopublico.registro_atendimento rgat inner join cadastro.localidade loca on loca.loca_id=rgat.loca_id inner join atendimentopublico.solicitacao_tipo_especificacao step on step.step_id=rgat.step_id where date(rgat_tmregistroatendimento) between '2007-01-01' and '2007-12-31' and rgat.step_id in (706,710,717,718,719,722,725,728,730,732,733,720,721,727,714,729) and rgat.rgat_cdsituacao=2 and date(rgat_tmencerramento) between '2007-01-01' and '2007-12-31' and rgat.rgat_tmencerramento rgat.rgat_tmregistroatendimento and rgat.loca_id=339 and rgat.rgat_id in (20111305, 30102409, 30102096, 30097214, 30102396, 20057815, 20325895) 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. thanks Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua cara @ymail.com ou @rocketmail.com. http://br.new.mail.yahoo.com/addresses
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general