Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread operations with time fields
Mon, May 5 2008 5:14 PMPermanent Link

"Andrej Bivic"
hello,
have two tields of time type. I need a query, which will compute the
difference. Something like this:

select date1, timefield1, timefield2, sum(cast(abs(timefield1 - timefield2)
as time))
from table1
group by day

pleasem, advise

Mon, May 5 2008 6:52 PMPermanent Link

Fernando Dias

Team Elevate Team Elevate

Andrej,

Since DBISAM doesn't have intervals (as EDB), the solution is a little
tricky... Please try this:

select
Date1,
cast(
 cast(HOURSFROMMSECS(sum(abs(timefield1-timefield2))) as varchar(2))+':'
 +cast(MINSFROMMSECS(sum(abs(timefield1-timefield2))) as varchar(2))+':'
 +cast(SECSFROMMSECS(sum(abs(timefield1-timefield2))) as varchar(2))
as Time)
from
  Table1
group by
  Date1


--
Fernando Dias
[Team Elevate]
Mon, May 5 2008 7:39 PMPermanent Link

"Robert"

"Andrej Bivic" <andrej.bivic@abitrade.si> wrote in message
news:17BAC676-9EAD-4BEC-9358-86CD99732518@news.elevatesoft.com...
> hello,
> have two tields of time type. I need a query, which will compute the
> difference. Something like this:
>
> select date1, timefield1, timefield2, sum(cast(abs(timefield1 -
> timefield2) as time))
> from table1
> group by day
>
> pleasem, advise

It's doable, but UGLY. Maybe someone has a better solution

select sum(t2 - t1) inmilli, cast('0:00' as time) zerotime into memory\temp
from times;
select cast((zerotime + inmilli) as time) final from memory\temp;

Robert

>

Mon, May 5 2008 7:43 PMPermanent Link

"Robert"

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:033ED068-BE6A-4F83-ACF5-2C64646E0887@news.elevatesoft.com...
>
>
> select sum(t2 - t1) inmilli, cast('0:00' as time) zerotime into
> memory\temp from times;
> select cast((zerotime + inmilli) as time) final from memory\temp;
>

The last cast is not needed

select zerotime + inmilli as final should do it

R

Mon, May 5 2008 7:54 PMPermanent Link

Fernando Dias

Team Elevate Team Elevate

Robert,

Your solution is really good, much better than mine, and it can be even
better:

select
  Date1, cast('00:00' as time) + sum(abs(timefield1-timefield2))
from
  Table1
group by
  Date1




--
Fernando Dias
[Team Elevate]
Image