good day to all.

i want to achieve list of rows for Average of encoder speed input.
i have this table name cocv_2004 and with the ff columns:
-- cocv_id
-- personality_id
-- province_code
-- municipality_code
-- votes
-- db_user
and
-- timestamp

can anyone help me how to select out from this table of an avegare speed refering from timestamp column? is there a way to compute time in sql queries function?

How could you possibly compute speed if you only
have one timestamp value? I don't understand... you'd
need to have two timestamps, one for start, one for
end, and then you could calculate the average over
the differences.


Cheers,
Tink

Hi thanks for the reply, Sorry im doop!

anyways here's my sencond table name cc_encodersee with columns:
-- cc_encodersee_id
-- cc_id
-- polparty_id
-- db_user
and
-- timestamp
...........................
now the two tables are here with both have time stamp? can u direct me how to make s calculation of average speed?

Not tested, but something along the lines of:

SELECT avg( a.timestamp - b.timestamp)
FROM cocv_2004 AS a
JOIN cc_encodersee AS b ON a.db_user = b.db_user

That's the only possible criteria I can think of :)


Cheers,
Tink

THANKS again for your reply.

i need your help again.
letsay i have this content on my table cc_encodersee:

--id--|----cc_id-------|----db_user----|--------- timestamp
------|-----------------|------------------|---------------------------------------
--3---|----91000-----|----gdilao-------|-- 2004-02-06 17:52:30.072632
--4---|----91000-----|----gdilao-------|-- 2004-20-06 19:00:08.420871
--5---|----91000-----|----gdilao-------|-- 2004-02-09 11:18:20.384175
--6---|----91000-----|----gdilao-------|-- 2004-02-09 11:31:54.352862
--7---|----91000-----|----gdilao-------|-- 2004-02-09 11:55:31.334232
--8---|----91000-----|----gdilao-------|-- 2004-02-09 12:07:32.137286
:
:
--25---|----91000-----|----gdilao-------|-- 2004-02-09 19:26:08.883724

can i select with the timestamp with a specific date given from a query to compute for an average
from table row 5 upto row 25 was 2004/02/09 of date?

at the back of my head i have a query like this but i know its not right.:
SELECT AVG(timestamp) FROM cc_encodersee WHERE timestamp = '2004-02-09' and db_user = 'gdilao';
just giving you an idea what i want to achieve. thanks again.

Your select is close enough ... does this work?


Cheers,
Tink

P.S.: I hope that you don't do this all for a living ;)

Hi i used the query you post but i got this weird error:
QUERY:
SELECT AVG(timestamp) FROM cc_encodersee WHERE to_char( timestamp,'YYYY-MM-DD') LIKE '2004-02-09%' and db_user = 'gdilao' AND (id BETWEEN 5 AND 25);
ERROR:
FUNCTION avg(timestamps) does not exist
Unable to identify the functions that satisfy the given argument types
You may need to add explicit typecast.

for sure its not an typo error issue on my query coz the column 'timestamp' was exist on my table but on error function it was reffering to 'avg(timestamps)' what this mean?

pls help again,
kriz

PS: im really sorry to bother you but i really get hard to read SQL tutorial right now. my boss just leave me on her desk to work this things out.