Dear Roy!

I want to ask that can I make Sum on Time field with any cast trick?

Now I solve this with Extracts, but may this solvable with easier way (what is handle half hours as 0.5), like our "Date AVG" forum topic?

select
gep,
nev gepnev,
max(idopont) as maxd,
min(idopont) as mind,
  sum(
     cast(
     (cast(
           (EXTRACT(hour from hossz) * 60 +
           EXTRACT(minute from hossz))
        as float) / 60) as float))
from termel
left join gep on (gep.kod=termel.gep)
where taska = 29

group by gep
order by gep


Thanks for your help:
  dd

durumdara


Looking at Tim's OLH you can CAST TIME AS

CHARACTER|CHAR
CHARACTER VARYING|VARCHAR
BYTE
BYTE VARYING|VARBYTE
TIME
TIMESTAMP

So there's nothing suitable there. I don't know what you're storing in the column but you may have a problem. TIME is the time of day and is limited to what is on a clock (that's why you can't really add it up) so if you may ever want to store times over 23 hours and 59 minutes you'll have a problem.

If you're storing duration which is what I'd guess I'd go for a simple FLOAT or NUMERIC column and store hours (and decimals). Much easier to work with then.


Roy Lambert

Try
SELECT SUM(FIELD - cast('0:0' as time))
FROM TABLE;

Rita


Freaky. I thought you didn't know SQL

Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:11D7DC2C-C17E-4C3D-80B8-EBC9F1A28E5B@news.elevatesoft.com...
>
> Freaky. I thought you didn't know SQL
>

I dont but I figured that out is it good ?
It worked for what I wanted.
Rita

<< I want to ask that can I make Sum on Time field with any cast trick? >>

Is the Time field supposed to represent an elapsed time ?  If so, then you
should consider using an HOUR-MSEC interval type instead:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Interval_Types

You can use any level of precision that you want:

HOUR TO MINUTE
HOUR TO SECOND
HOUR TO MSECOND

And then adding or subtracting these values will work just fine, always
giving you the correct totals in terms of number of hours, minutes, seconds,
and milliseconds.   Furthermore, you can CAST the values between the various
precisions, depending upon your needs.

--
Tim Young
Elevate Software
www.elevatesoft.com

Rita

>I dont but I figured that out is it good ?
>It worked for what I wanted.

It works, but I sort of feel that it shouldn't. A TIME of 25:57 just doesn't look right

Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:7947C0D9-3CF3-4204-AADA-D3F7AE3C8EA8@news.elevatesoft.com...
>
> It works, but I sort of feel that it shouldn't. A TIME of 25:57 just
> doesn't look right
>

It does if you are dealing in appointments like my cab/courier app.
I know since when has a taxi been ontime but the code is ok
the drivers just blame the computers.
One other SQL u answered in Roy was DBISAM.SQL dated
17/08/2009 titled convert to dynamic. I just updated that, comments
please good or bad.

Rita