Thread: 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
Paulo Moraes
Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua cara @ymail.com ou @rocketmail.com.
I change form to create query but basically need
sum(rgat.rgat_tmencerramento - rgat.rgat_tmregistroatendimento) = result in hours
sumary:
rgat.rgat_tmencerrament = timestamp column
gat.rgat_tmregistroatendimento = timestamp column
De: Osvaldo Kussama <osvaldo.kussama@gmail.com>
Para: paulo matadr <saddoness@yahoo.com.br>
Enviadas: Sexta-feira, 7 de Novembro de 2008 12:50:14
Assunto: Re: [SQL] sum timestamp result in hours
2008/11/7, paulo matadr <saddoness@yahoo.com.br>:
> 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.
>
88 days? 51 hours?
Try:
bdteste=# SELECT EXTRACT(EPOCH FROM INTERVAL '1 year 4 mons 88 days
51:42:00')/3600;
?column?
----------
13809.7
(1 registro)
Osvaldo
Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua cara @ymail.com ou @rocketmail.com.
On Fri, Nov 7, 2008 at 9:36 AM, paulo matadr <saddoness@yahoo.com.br> wrote:
> I change form to create query but basically need
> sum(rgat.rgat_tmencerramento - rgat.rgat_tmregistroatendimento) = result in
> hours
> sumary:
> rgat.rgat_tmencerrament = timestamp column
> gat.rgat_tmregistroatendimento = timestamp column
select extract('epoch' from '4 days 6 hours 3 minutes'::interval)*
interval '1 sec';