Обсуждение: 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.
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